Dynamically use variables in SQL in EXECUTE

7 Jun 2006 7:37 PM

stuart.karp

Hi,

What I want to do is:

DECLARE @sqlName varchar(255)
DECLARE @temp NVARCHAR(100)

SET @sqlName =(select name from master.dbo.sysdatabases where name like
'Job_%')
SET @temp = 'USE ' + RTRIM(@sqlName)

PRINT @sqlName
EXEC (@temp)
GO

--rest of my SQL code

--

Now basically I am going to have this script to run on multiple
databases where the database could be something different.
ex.
Computer1 - DB: Job_1234
Computer2 - DB: Job_5678

Before I run my code I want to make sure it runs under the correct
database. It finds the right database using select name from
master.dbo.sysdatabases where name like 'Job_%'
but how do I execute the USE @temp statement. It says it executes, but
it still displays the master database in Query Analyzer. Any ideas on
how to do this? I just basically need to get this dynamic USE
statement to work. Thanks in advance.

7 Jun 2006 7:50 PM

David Portas

stuart.k***@gmail.com wrote:
Show quoteHide quote

> Hi,
>
> What I want to do is:
>
> DECLARE @sqlName varchar(255)
> DECLARE @temp NVARCHAR(100)
>
> SET @sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @temp = 'USE ' + RTRIM(@sqlName)
>
> PRINT @sqlName
> EXEC (@temp)
> GO
>
> --rest of my SQL code
> --
>
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
>
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.

Your code should work but the USE is scoped to the EXEC statement. Once
the EXEC is done you are returned to where you started. You need to put
some other code into the EXEC string as well if you want it to execute
in the context of another database.

EXEC is a pretty useless tool for this kind of thing. It's much easier
to parameterize the database in a connection string or at the OSQL
command prompt.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

7 Jun 2006 8:10 PM

stuart.karp

Thanks a lot.

This worked if I ran something like

EXEC (@temp + ' ' + @code)
where @code is the rest of my code that I wanted to run. I would use
OSQL if I could but unfortunately I can't.

Thanks again for your quick response.

-Stu

David Portas wrote:
Show quoteHide quote

> stuart.k***@gmail.com wrote:
> > Hi,
> >
> > What I want to do is:
> >
> > DECLARE @sqlName varchar(255)
> > DECLARE @temp NVARCHAR(100)
> >
> > SET @sqlName =(select name from master.dbo.sysdatabases where name like
> > 'Job_%')
> > SET @temp = 'USE ' + RTRIM(@sqlName)
> >
> > PRINT @sqlName
> > EXEC (@temp)
> > GO
> >
> > --rest of my SQL code
> > --
> >
> > Now basically I am going to have this script to run on multiple
> > databases where the database could be something different.
> > ex.
> > Computer1 - DB: Job_1234
> > Computer2 - DB: Job_5678
> >
> > Before I run my code I want to make sure it runs under the correct
> > database. It finds the right database using select name from
> > master.dbo.sysdatabases where name like 'Job_%'
> > but how do I execute the USE @temp statement. It says it executes, but
> > it still displays the master database in Query Analyzer. Any ideas on
> > how to do this? I just basically need to get this dynamic USE
> > statement to work. Thanks in advance.
>
> Your code should work but the USE is scoped to the EXEC statement. Once
> the EXEC is done you are returned to where you started. You need to put
> some other code into the EXEC string as well if you want it to execute
> in the context of another database.
>
> EXEC is a pretty useless tool for this kind of thing. It's much easier
> to parameterize the database in a connection string or at the OSQL
> command prompt.
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --

8 Jun 2006 4:17 AM

hongju

You want to change context switching.
You can search sp_executeresultset on SQL Server 2000 SP3 later.
Not S2K5.
You can use below sample query.

DECLARE @PROC NVARCHAR(4000)
SET @PROC ='job_1234' + '.DBO.SP_EXECRESULTSET'
EXEC @PROC @SQLSTMT

"stuart.k***@gmail.com"ë‹˜ì´ ìž‘ì„±í•œ ë‚´ìš©:

Show quoteHide quote

> Hi,
>
> What I want to do is:
>
> DECLARE @sqlName varchar(255)
> DECLARE @temp NVARCHAR(100)
>
> SET @sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @temp = 'USE ' + RTRIM(@sqlName)
>
> PRINT @sqlName
> EXEC (@temp)
> GO
>
> --rest of my SQL code
> --
>
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
>
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
>
>