Pass DB name as parameter to a SQL statement

Question

Post reply

Pass DB name as parameter to a SQL statement

a_ud

SSCrazy

Points: 2457
More actions
November 22, 2010 at 12:02 pm

#229452

Take a SQL statement such as this one:
SELECT AuditMasterID, TableName
FROM dbDRCT_1.dbo.tblAuditMasterLog
I tried today to pass the whole string: 'dbDRCT_1.dbo.tblAuditMasterLog' as a parameter (defined previously as VARCHAR(50)), but it doesn´t work.
Questions:
1) Why SELECT doesn´t work in a structure like that, such as SELECT .. from varchar(50), where VARCHAR is a string with the name of the object(a table in a database)? Don´t see the logic.
2) Is there an easy way to do this, for instance, calling a function that returns dbDRCT_1 inside the SELECT? That is:
SELECT AuditMasterID, TableName
FROM <function returning dbDRCT_1>.dbo.tblAuditMasterLog
Thanks all in advance!! Al

Viewing 7 posts - 1 through 6 (of 6 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

What you're asking about is called "dynamic SQL".

You'll need to build the query as a string, and then use sp_executesql or exec() to run it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

John Rowan SSC Guru Points: 56440 More actions · Answer 2

1) Theres probably more reasons why you wouldn't want it to work this way than there are for working this way. Here are a few....

-- Dependancy checking in SPs would no longer work....when making changes to your tables, you would no longer have the abililty to determine impact on your code.

-- Compiler/optimizer problems....how could you ever compile/syntax check your code when the code itself cannot tell you the DB object that you are referencing? How could you store query plans for a structure like that?

2) Dynamic SQL....here's a great link on the topic.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Brigadur SSCrazy Points: 2097 More actions · Answer 3

Hi,

Simplest example:

declare @db_name nvarchar(50);

set @db_name = db_name(); -- gets name of current db

execute('SELECT AuditMasterID, TableName FROM ' + @db_name + '.dbo.tblAuditMasterLog')

Cheers

LutzM SSC Guru Points: 107049 More actions · Answer 4

You would need dynamic SQL for this. Something lik the following:

DECLARE @database NVARCHAR(128)='dbDRCT_1'

DECLARE @sql NVARCHAR(2000)

SET @sql='SELECT *

FROM ' + @database +'.dbo.tblAuditMasterLog'

PRINT @sql

--EXEC (@sql)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

a_ud SSCrazy Points: 2457 More actions · Answer 5

Thanks to all, to SSC-Enthusiastic just a little detail that he couldn´t know, it was to a different database. But surely from your 3 replies there come out a perfect solution.

GSquared SSC Guru Points: 260824 More actions · Answer 6

If the value will come in any way from user input, make sure to check it against sys.databases, before you build it into a string you're going to execute.

First, it helps prevent errors from typos.

Second, it helps prevent SQL injection.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon