How to do a USE statement inside a stored proc?

microsoft.public.sqlserver.programming

2 Mar 2006 8:28 PM

Juan Dent

Hi,

I have a general purpose stored proc that could be used in several
databases. I need to tell it what database to work on. My first solution was
to use the 'USE @DB' statement at the beginning of the stored proc, where @DB
was a parameter passed in. Yet this does not work!

I remember having done this before, but don't recall exactly how!
Any suggestions?

--
Thanks in advance,

Juan Dent, M.Sc.

2 Mar 2006 8:38 PM

Aaron Bertrand [SQL Server MVP]

EXEC('USE '+@db+'; do something');

Please read http://www.sommarskog.se/dynamic_sql.html

Show quote

"Juan Dent" <Juan_Dent@nospam.nospam> wrote in message
news:B8CDCB05-C049-422E-AC38-60A97D7D6CCA@microsoft.com...
> Hi,
>
> I have a general purpose stored proc that could be used in several
> databases. I need to tell it what database to work on. My first solution
> was
> to use the 'USE @DB' statement at the beginning of the stored proc, where
> @DB
> was a parameter passed in. Yet this does not work!
>
> I remember having done this before, but don't recall exactly how!
> Any suggestions?
> --
> Thanks in advance,
>
> Juan Dent, M.Sc.