Use SQL Varriables with the "use" command.

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 2005 Forums

Transact-SQL (2005)

Use SQL Varriables with the "use" command.

Author

Topic

wopfather
Starting Member

2 Posts

Posted - 2007-05-25 : 15:21:41

I am writing a custom query that can be generated against any database in the system. is there a way I can get this code to work.

declare @dbname as nvarchar(50)
set @dbname = 'mydb'
use @dbname

When this executes, I get the below message.

Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@dbname'.

Can the "USE" statement be used in this context

Also, is there a way SQL can prompt me to enter the varriable dbname during execution?

Dino-

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-25 : 15:25:43

1. no.

2. execute from where? SSMS?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-26 : 01:48:11

1
Why do you want to pass db name as parameter?
http://www.sommarskog.se/dynamic_sql.html

2
Use front end

Madhivanan

Failing to plan is Planning to fail

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-26 : 03:06:08

if you have a script you want to execute in several different databases, you can use sqlcmd.exe to do it (i do this all the time):


for /F "tokens=1 delims=," %%i in (databases.txt) do @(
	sqlcmd.exe -d %%i -S MYSERVER -E -i myquery.sql
)

where databases.txt is a flat file with a different database name on each line.

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources