Dynamic SQL HELP

Question

Post reply

Dynamic SQL HELP

Jeffery Williams

SSCertifiable

Points: 6441
More actions
April 5, 2010 at 1:38 pm

#213478

Could someone please give me a working sample?
I need to set a variable in a SP that will contain the name of a DB. THEN throughout the SP I need to do SELECT's from tables in the stated DB. These selects for the most part will be the result of a preceding insert statement or maybe some logic.
I can't get the dynamic SQL to work for the DB name. In short this is what it would look like (in a NON dynamic SQL way, since I can't seem to accomplish that).
DECLARE @SourceDB as varchar(50)
SET @SOurceDB = 'MyDB'
Select Field1, Field2, Field3, etc...
FROM @SourceDB.dbo.ThisTable
Any recommendations here?
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 1

DECLARE @SourceDB as varchar(50)

DECLARE @sql nvarchar(max)

SET @SOurceDB = 'MyDB'

SET @sql = 'Select Field1, Field2, Field3 FROM ' + @SourceDB + '..MyTable'

print @sql -- To view the query prior to executing

EXEC sp_sqlexec @sql

Converting oxygen into carbon dioxide, since 1955.

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 2

Thank you.. Perfect.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

david_wendelken SSCrazy Points: 2388 More actions · Answer 3

The example given uses dynamic sql.

The sql statement is held in a variable and is built by concatenating strings together.

At some point, you are going to be getting the name of the database to use from "somewhere".

If that somewhere is a "user-supplied text value", you will have just created a unit of code that is hacker-friendly. Read up on "sql injection attacks".

You need to find some way to restrict the values that are to be used as string input into the sql statement. Otherwise, hackers can quiery data they aren't supposed to see, or alter or destroy your database.