Dynamic SQL USE statement not working

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

SQL Server Development (2000)

Dynamic SQL USE statement not working

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-11-30 : 18:10:04

Heya SQLites,

I'm trying to set the database dynamically via a USE statement, but it does't seem to work via dynamic SQL in an EXEC statement

Example:
DECLARE @DATEABASE VARCHAR(128), @DYNAMO VARCHAR(1000)

SELECT @DATABASE = (SELECT DATABASENM FROM CLIENTS WHERE CUSTID = @CUSTID)

SET @DYNAMO = 'USE [' + @DATABASE + ']'
EXEC(@DYNAMO)

Thoughts?

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-11-30 : 18:26:06

Are you getting a syntax error? Are you questioning the scope of your USE statement? Not sure what your question is.

Review the following:



use AdventureWorks
go

select db_name() DatabaseName_1

declare @DBname varchar(10),
	@cmd	varchar(100)

set @DBName = '[master]'
set @cmd = 'use ' + @DBName + ' select db_name() DatabaseName_2'

exec(@cmd)

select db_name() DatabaseName_3

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-11-30 : 18:29:42

also... you have a syntax error (just a typo?):


DECLARE @DATEABASE
...
SELECT @DATABASE

Nathan Skerl

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-11-30 : 18:36:17

that's a typo, trying your logic now thanks nathans

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-30 : 23:30:26

it doesn't work because dynamic sql statements execute in their own scope. that is, this works:

use master
exec('use Northwind select * from Orders')

but this doesn't (because the select statement is executed in the context of master)

use master
exec('use Northwind')
select * from Orders

take a look here for the bible of dynamic sql:
http://sommarskog.se/dynamic_sql.html

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources