USE DATABASE doesn't use database

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 doesn't use database

Author

Topic

HellBoy
Starting Member

9 Posts

Posted - 2007-12-27 : 10:08:24

Declare @DBName sysname
Set @DBName = 'Pubs'
EXEC ('USE '+@DBName+';
DECLARE Header CURSOR FOR
SELECT * FROM dbo.titles')

I want to use the DBname variable with cursor but it wasn't work

SkyDiver

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-27 : 10:12:50

quote:
Originally posted by HellBoy

Declare @DBName sysname
Set @DBName = 'Pubs'
EXEC ('~~USE '+@DBName+';~~
DECLARE Header CURSOR FOR
SELECT * FROM '+@DBName+'.dbo.titles')

I want to use the DBname variable with cursor but it wasn't work

SkyDiver

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

HellBoy
Starting Member

9 Posts

Posted - 2007-12-27 : 10:22:49

Declare @DBName sysname
Set @DBName = 'Pubs'
DECLARE Header CURSOR FOR
SELECT * FROM '+@DBName+'.dbo.titles')

I tried to use this but not work
- I used SQL Server 2000

SkyDiver

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-27 : 10:24:10

I don't think that will work either. Your cursor will only be "in scope" for the exec'd process. any variable declared in dynamic sql (including a cursor) will only be available within that process.

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-27 : 10:24:56

tell us why you want to do this and we'll tell you of a better way if there is one.

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

HellBoy
Starting Member

9 Posts

Posted - 2007-12-27 : 10:29:04

I made a Procedures to insert data from table to another and i used a cursor function and i want to use the database name as dynamic name.

SkyDiver

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-27 : 10:39:08

TG,

I think the scope of cursor is Global by default which means it can be accessed outside EXEC.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-27 : 10:58:27

harsh, you're right. I just tested it.

I never tried to do that before and just assumed you couldn't.


declare @title_id varchar(25)

exec('
declare crs cursor for 
select title_id from pubs..titles
')

open crs
fetch next from crs into @title_id
while @@fetch_status = 0
begin
	print '@title_id: ' + @title_id
	fetch next from crs into @title_id
end
close crs
deallocate crs

Be One with the Optimizer
TG

HellBoy
Starting Member

9 Posts

Posted - 2007-12-27 : 11:00:10

Declare @DBName sysname
Set @DBName = 'Pubs'

DECLARE Header CURSOR FOR
SELECT * FROM [DBname].dbo.titles

What can i do to use dynamic Database name in this case

SkyDiver

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-27 : 11:04:09

to expand TG's code:


Declare @DBName sysname
Set @DBName = '[Pubs]'
declare @title_id varchar(25)

exec('
declare crs cursor for 
select title_id from ' + @DBName + '.dbo.titles
')

open crs
fetch next from crs into @title_id
while @@fetch_status = 0
begin
	print '@title_id: ' + @title_id
	fetch next from crs into @title_id
end
close crs
deallocate crs

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-27 : 11:05:44

That's what I initially suggested, not sure why it didn't work for HellBoy though.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

HellBoy
Starting Member

9 Posts

Posted - 2007-12-27 : 11:41:11

Thanks for all,
it works now harsh.

SkyDiver

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources