Is this possible? Use @DBVarableName

Question

Post reply

Is this possible? Use @DBVarableName

Jeremy Conway

Grasshopper

Points: 16
More actions
April 3, 2005 at 8:13 pm

#68816

Hi I was wondering if the following statement was possible in SQL Server 2000. When attempting to run it I receive the command was completed successfully. However, it does not appear that the following commands use the database that is specified in the statement.
DECLARE @DBName NVARCHAR (30)
SET @ DBName = 'TestDatabase'
EXEC ('USE ' + @DBName)
(Rest of script goes here...)

Anyway ideas on how I can modify it so that it works???

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

wz700 SSCertifiable Points: 7133 More actions · Answer 1

it works fine.

Exec has it own session. If you expect (Rest of script goes here...) to be the same session as Exec. put them together.

e.g.

DECLARE @DBName NVARCHAR (30), @sqlcmd varchar(100)

SET @ DBName = 'pubs'

Set @sqlcmd = 'select * from authors'

EXEC ('USE ' + @DBName+';'+@sqlcmd)

Jeremy Conway Grasshopper Points: 16 More actions · Answer 2

Thanks wz700,

I did not know that when a exec statement was run it ran a new session. So thankyou for that.

However, where I have got (Rest of script goes here...) there is a lot of script to follow which all should be run within the database that I am setting in the database variable. It would be hard to follow if this was to be put into a variable name and used the way which you suggested.

eg.

EXEC ('USE ' + @DBName+';'+@sqlcmd)

As the script which I am creating contains numerous instances whereby the database which it is using must be changed I was hoping to find a way where all subsequent commands that follow the USE @dbname statement use the specified database.

Eg.

DECLARE @DBName1 NVARCHAR (30), @DBName2 varchar(30)

SET @ DBName1 = 'pubs1'

SET @ DBName1 = 'pubs2'

EXEC ('USE ' + @DBName1)

(Lots of SQL Statements referencing the Pubs1 db)

EXEC ('USE ' + @DBName2)

(Lots of SQL Statements referencing the Pubs2 db)

etc....

I do not want the @DBName Variables hardcoded because the script must change databases numerous times throughout its execution and be easily modified for different environments.

Any ideas?

wz700 SSCertifiable Points: 7133 More actions · Answer 3

One way to workaround

declare @DBName varchar(200)

set @DBName = 'northwind'

if @DBName = 'pubs'

use pubs

else

use northwind

select count(*) tbno, db_name() from sysobjects where xtype = 'u'

SpiceDBA SSC Veteran Points: 221 More actions · Answer 4

Here is a script that I got from this web site to backup all the databases. If your running the same script on all the databases, create a procedure and replace <stored Procedure> with your stored procedure name.

declare

@IDENT INT,

@sql varchar (50),

@DBNAME VARCHAR(200)

select @IDENT=min(DBID)

from SYSDATABASES

WHERE [DBID] > 0 AND

NAME NOT IN ('master', 'model', 'msdb', 'PUBS', 'NORTHWIND', 'TEMPDB')

while @IDENT is not null

begin

SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT

/*Change disk location here as required*/

Select @sql = 'Use '+@DBNAME

Exec <stored Procedure>

select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 and DBID>@IDENT AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')

end

FYI: Looking through the scripts on this website has been extremely useful...