Using Variable with Use <databasename>??????? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Using Variable with Use <databasename>???????

Author

Topic

jun0
Starting Member

32 Posts

Posted - 2013-06-04 : 06:45:58

hello guys,

I was recently very shocked to hear that a variable can not be passed to a SQL query after the USE statement. For example, I would like to pass the database name to

USE <databasename>

where <databasename> is the variable but the query then doesn't work.
I'm trying to do all this in SQL, how do people get around this? this seems like a big floor!

thankyou guys

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-04 : 06:50:34

You can access any database object by using DatabaseName..ObjectName instead of USE DBName

--
Chandu

jun0
Starting Member

32 Posts

Posted - 2013-06-04 : 06:58:31

Sorry what exactly do you mean, can you show this in an example....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 07:01:11

quote:
Originally posted by jun0

hello guys,

I was recently very shocked to hear that a variable can not be passed to a SQL query after the USE statement. For example, I would like to pass the database name to

USE <databasename>

where <databasename> is the variable but the query then doesn't work.
I'm trying to do all this in SQL, how do people get around this? this seems like a big floor!

thankyou guys

why do you want to do this?
Can you explain your business case? why is it that you cant determine db beforehand?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-04 : 07:28:07

quote:
Originally posted by jun0

Sorry what exactly do you mean, can you show this in an example....

I thought you are trying to access another database objects from current DB.....
Tell us what exactly you are trying to do

--
Chandu

jun0
Starting Member

32 Posts

Posted - 2013-06-04 : 07:40:32

I have a sql query, saved as a sql file. and i want to read a variable in as a parameter, the variable will be obtained from another file. the databasename is the variable.

also, if i just write the query in sql server as a test, if i declare the variable at the top of the query then write:


USE <dbnamevariable>

select * from sample table

the query doesn't work. how can this be gotten round, in both scenarios?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-04 : 07:56:46

For this kind of requirement you have to use dynamic query

DECLARE @DBNAME VARCHAR(50), @DynSQL VARCHAR(MAX) = ''
SET @DBNAME = 'TEST'
SET @DynSQL = ' SELECT * FROM ' + @DBNAME + '..SampleTable; '
EXEC (@DynSQL)

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 08:11:29

quote:
Originally posted by bandi

For this kind of requirement you have to use dynamic query

DECLARE @DBNAME VARCHAR(50), @DynSQL VARCHAR(MAX) = ''
SET @DBNAME = 'TEST'
SET @DynSQL = ' SELECT * FROM ' + @DBNAME + '..SampleTable; '
EXEC (@DynSQL)

--
Chandu

better to use sp_executesql and avoid possibility of sqlinjection

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-06-05 : 00:21:52

Since you have it saved as a SQL file, you can run it over a list of databases from a command prompt using SQLCMD. There's a blog article here with examples:

http://weblogs.sqlteam.com/robv/articles/4102.aspx

Replace references to "osql" with "sqlcmd", the syntax is otherwise the same. You'd pass the database name with the -d switch (modified example from blog post):


for /F %b in (databases.txt) do sqlcmd -E -n -S server -d %b -i scriptfile.sql

The sqlcmd utility is documented here: http://msdn.microsoft.com/en-us/library/ms162773.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 00:28:14

quote:
Originally posted by robvolk

Since you have it saved as a SQL file, you can run it over a list of databases from a command prompt using SQLCMD. There's a blog article here with examples:

http://weblogs.sqlteam.com/robv/articles/4102.aspx

Replace references to "osql" with "sqlcmd", the syntax is otherwise the same. You'd pass the database name with the -d switch (modified example from blog post):

for /F %b in (databases.txt) do sqlcmd -E -n -S server -d %b -i scriptfile.sql
The sqlcmd utility is documented here: http://msdn.microsoft.com/en-us/library/ms162773.aspx

thats a nice option

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources