'use database' statement & dynamic sql

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 database' statement & dynamic sql

Author

Topic

snomad
Starting Member

22 Posts

Posted - 2009-08-11 : 09:19:47

Hi all,

Any ideas why this won't work?

declare @primary sysname
select @primary = 'db1'
declare @sql varchar(50)
select @sql = 'use '
select @sql = @sql + @primary
-- select (@sql)
exec (@sql)

the select @sql will return 'use db1' & the exec gives no error but the db context does not change.

thank you in advance!! this is doing my head in.

Thank you!!

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-11 : 09:29:09

Yes, it does.

DECLARE @temp VARCHAR(255)
SET @temp = 'use master;select db_name()'
EXEC (@temp)

An infinite universe is the ultimate cartesian product.

snomad
Starting Member

22 Posts

Posted - 2009-08-11 : 11:37:40

thanks for the quick reply!!

you're right, it works but it is not persistent.

DECLARE @temp VARCHAR(255)
SET @temp = 'use master;select db_name()'
EXEC (@temp)
-- (will return 'master')
SELECT db_name()
-- (outside of dynamic sql will return orig db)

I need the use statement to persist for the rest of the script - to switch dbs. Just a way of saying:
declare @input sysname
select @input 'db1'
use @input

Sorry if I wasn't clear the 1st time. Hope I am now!

Thank you!!

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-11 : 14:03:45

Then either do it before you use EXEC(if in a query window) or in the same call of EXEC as I did in my example.

An infinite universe is the ultimate cartesian product.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-08-11 : 19:50:09

Dynamic SQL has the characteristic that it operates within its own context. This means that when you EXEC('use master') it leaves your context, executes the command (and actually changes to the master database) but then returns from to your context where the current database is in use. It's kind of like calling a subroutine that sets a local variable in that it does set the variable but when it returns, the local variable is not available to the calling procedure.

HTH

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources