Dynamic SQL and USE DatabaseName

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)

Dynamic SQL and USE DatabaseName

Author

Topic

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-12-11 : 17:47:00

I've been asked to look at this. I know how to use dynamic sql but what they are asking I'm not sure about. They have some t-sql (a big procedure) code and at the top of it they want to say what db to use for it. So they pass in the db to use, it does an exec (@sql) and changes the db to the appropriate one, then they want it to run the code below that in the database passed in. I told them that they would have to put the t-sql code (big procedure) inside of the @sql to execute it but they don't want to do it that way.

Here's an example of what they have:

select @sql = 'use Product_database'
exec (@sql)
select * from product_table

This doesn't work because it's still in the database in the connection that was originally established (so it can't find the product_table).

Here's what I suggested:

select @sql = 'use product_database
select * from product_table'
exec(@sql)

This works. However 'select * from product_table' is just an example. They have a huge procedure to follow and don't want to try to put it in @sql to execute it. Is there any way to do this their way (1st example)?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 17:54:21

select @sql = 'use product_database; select * from product_table'
exec(@sql)

E 12°55'05.25"
N 56°04'39.16"

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-12-11 : 18:01:38

Yea, that works but that was just an example. Their code to follow the "use database" is huge and they want only a "use database" and have it change the context and then execute the code below that (without putting the code into the @sql variable). Basically, can you use dynamic sql to change the context of the connection. So that after doing an "exec (@sql)", your db changes in your connection and any code below that would get ran from the db in your @sql. Like the first example I listed above.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 18:06:59

Instead of putting USE first, just prefix the table names with the database name too and make heavy use of alias names to shorten down the code.

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-12-11 : 18:08:25

The simple answer is no, you can't do that.

CODO ERGO SUM

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-12-11 : 18:11:43

Yea, I understand what you are saying to do. I'm not even sure what their code is they just asked me how to make dynamic sql and the "use database" command be able to change the context. I assume they might want to use the code to run it in different dbs that they want to pass to the code. So I'm stuck at wondering if it's possible to have the "use db" with dynamic sql to change the context of the connection. Any other ideas on how to change the context on the fly?

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-12-11 : 18:15:02

quote:
Originally posted by Michael Valentine Jones

The simple answer is no, you can't do that.

CODO ERGO SUM

LOL I didn't think so, but wondered if anyone had ran into the same problem and found a way to do it. Like I said, it's not my code and they don't want to change their code. They just want to be able to change the context on the fly. It seems like a simple thing to do, but evidently not.

Haywood
Posting Yak Master

221 Posts

Posted - 2007-12-11 : 18:40:34

Here's a simple example of how to USE in dynamic sql..

[CODE]
DECLARE @DBName sysname
, @DynSQL varchar(256)

SET @DBName = 'Foo'

SET @DynSQL = '

USE [' + @DBName + ']

SELECT *
FROM dbo.FooTable'

EXEC (@DynSQL);
[/CODE]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-12 : 01:08:14

All about Dynamic SQL
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-12-12 : 10:46:34

quote:
Originally posted by Haywood

Here's a simple example of how to USE in dynamic sql..

[CODE]
DECLARE @DBName sysname
, @DynSQL varchar(256)

SET @DBName = 'Foo'

SET @DynSQL = '

USE [' + @DBName + ']

SELECT *
FROM dbo.FooTable'

EXEC (@DynSQL);
[/CODE]

Yea, but what if you wanted to do the "select * from dbo.footable" outside of the dynamic sql after the USE @dbname was executed? That's what I'm asking. I don't think it's possible. If you read my original question, it states that there is a huge procedure (it's 100's or 1000's of lines) of code to execute after the USE @dbname. They don't want to put that huge piece of code into the @DynSQL variable. They want to be able to change the context to the db of their choice and then execute the huge block of code after that.

So in other words, is there a way to do this (the connection this would originally run from isn't to the Foo db):

DECLARE @DBName sysname
, @DynSQL varchar(256)

SET @DBName = 'Foo'

SET @DynSQL = ' USE [' + @DBName + '] '

EXEC (@DynSQL)

SELECT *
FROM dbo.FooTable'

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-12-12 : 11:07:26

If they want to do that, then why not just put the procedures in the database they want to execute them in?

CODO ERGO SUM

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-12-12 : 11:21:05

I agree totally. I'm with you guys on this. I was just asked how to do it and I'm seeing if there is a way. For arguements sake, just assume there are 500 dbs with the exact same structure and now they have a big block of code they want to run in a database. They don't what to put a copy of the code in each db and have 500 copies to maintain. They just want to pass it which db to run from dynamically with t-sql. The block of code is too big to put in the @sql variable with the "USE db" statement. Just need a way to dynamically change the context...maybe there are other ways to change the context other than USE with t-sql. Not sure.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-12 : 11:23:46

see if this helps you:
http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx

i don't really reccomend this in a production environments but sometimes you have to hack things...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-12-12 : 11:42:30

quote:
Originally posted by Van
...The block of code is too big to put in the @sql variable with the "USE db" statement...

In SQL 2005, your SQL variable can be nvarchar(max), so I don't think that is a limitation. You can also use multiple @sql variables in an execute statement.

Here is another method you can use to change datebase context, without the USE statement.


use msdb

exec sp_executesql		N'select DB_1=convert(nvarchar(30),db_name())'
exec tempdb..sp_executesql	N'select DB_2=convert(nvarchar(30),db_name())'


Results:
DB_1                           
------------------------------ 
msdb

(1 row(s) affected)

DB_2                           
------------------------------ 
tempdb

(1 row(s) affected)

CODO ERGO SUM

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-12 : 11:42:51

another option, which is not a hack, would be to execute the script from sqlcmd, where you pass the database name in the -d flag. if you have a text file listing all the databases you want to run the script in, just do this from a .bat file:


for /F "tokens=1 delims=," %%i in (all_my_databases.txt) do (
	set CURRENT_DATABASE=%%i
	SQLCMD -d %CURRENT_DATABASE% -i myscript.sql -o %CURRENT_DATABASE%_results.txt -SMYSERVER -E
)

you could even use bcp to generate the all_my_databases.txt file to automate that part:


bcp "select name from master.dbo.sysdatabases where ..." queryout all_my_databases.txt -c -T -SMYSERVER

elsasoft.org

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-12-12 : 11:51:51

Thanks for the replies. I'll come up with something for them.

Haywood
Posting Yak Master

221 Posts

Posted - 2007-12-12 : 12:18:43

Outside of compiling in the master and marking as a system object (which still may not work), I think Jezemine's solution is about the best if you want to avoid the dynamic sql.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources