[dba-SQLServer] Dynamic SQL

Hi John,

Dynamic SQL seems like the obvious solution in your circumstance.
If you are executing the strings from code then another option is to
build some generic global
sprocs on your server.

I would do something like...

USE MASTER
GO
CREATE PROCEDURE dbo.DeleteFrom (
	@DB nvarchar(100), 
	@table nvarchar(100)
)
AS
DECLARE @SQL [nvarchar](4000)

IF @DB <> N'' AND @table <> N''	
	BEGIN
	SET @SQL = 'DELETE FROM ' + @DB + '.' + LTRIM(RTRIM(@table))
	PRINT @SQL
	EXEC sp_executesql @SQL
	PRINT @@ROWCOUNT
	END 


And call it like 
EXEC  [master].[dbo].[DeleteFrom] 'databasename', 'dbo.tablename'

HTH

Michael M



I know that dynamic SQL is frowned upon for obvious reasons.  Please
phrase your responses understanding the following:

1) I work by myself, there are no other DB users.
2) My network is connected through a "consumer grade" router / firewall
to the internet.
3) I run Avs, software firewalls and the newer "zero day" malware
detectors such as Counterspy and Threatfire.
4) I have never (to my knowledge) been infected by any viruses, key
loggers, Trojans or other such malware.
5) There ARE other computers in the house that also connect to the
internet, specifically my wife's laptop and my son's laptop.  Both of
them run Avs and software firewalls.

I am trying to build SPs and such to perform functionality such as 

	Delete * from tblTemp
	Bulk Insert 'SomeDbName.SomeTblName' from 'SomeFilePath'

Obviously if I am going to use this to process files in my many
different databases then I will need a way to pass in SomeDbName,
SomeTblName, and SomeFileName.  ATM I am doing this by using dynamic
SQL, i.e. building up SQL strings and then Executing those strings.  It
all seems to be working.

I am SPECIFICALLY ASKING for "best practices" couched within the
situation that I work in.  I am not using a database that directly faces
the internet or is ever touched (to my knowledge) by any outsiders.
Even considering my situation, if the "better way" is actually
attainable (by ME, and you already know what THAT means) I will do
things the better way. 

1) Is there another way besides the dynamic SQL?
2) Is this "other way" generic, can it be applied to all of my
databases, or would I need to store the same SP in each db and use local
table names.  Or would I need to do something else entirely.

Please please do not respond with how to handle this for an internet
company taking orders from the internet or an IBM with 6 thousand direct
users of the database.  But please do let me know if or why I should
take the same precautions anyway and what those precautions would be IF
I really need to be doing more.  I am asking for all opinions, and yet I
am still asking you to consider my circumstances and offer your opinions
based around that.

Obviously I want to be as secure as I can be while accepting the fact
that it is just lonely me with no dedicated security guru.

Thanks,

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com