SQL Server Performance

"USE Database" statement in a Stored Proc

Discussion in 'General Developer Questions' started by TurnerBurn, Sep 2, 2005.

  1. TurnerBurn New Member

    Hi all,

    I'm wondering if there is a way to have something like a "USE @database" statement in a stored procedure. I realize at the start that SQL Server does not allow a USE statement in a stored proc, but I'm trying to find a way to have a stored proc residing in 1 database and then tell it at execution time to use a different database. (My app has to service multiple databases and I don't want to have to copy the proc in 7 different databases)

    Any way around this limitation?

    Thanks,

    Karl
  2. satya Moderator

    Invoke the SP(s) using dynamic SQL like so:<pre id="code"><font face="courier" size="2" id="code"><br /> USE master<br /> GO<br /> DECLARE @dbname VARCHAR(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /> SET @SomeOtherDb = 'test1'<br /> EXEC ('EXEC ' + @dbname + '.dbo.SP1')</font id="code"></pre id="code"><br /><br /><br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  3. Adriaan New Member

    Of course Satya's work-around is excellent - but you must understand the consequences. You need to add this sp to the master database, and so you also need to either keep a copy of master including the sp, or remind yourself that if at one point you must do a server restore, you have to add the sp to the master db again.
  4. FrankKalis Moderator

    If you want something like the shipped procedures, you would need to create the SP in the master db and prefix its' name with sp_*. If you invoke then the proc while you're curently in another db, it should work on that current db.
    Adriaan comment about "complicating" things is good. If this is for your own administrative stuff, what about having a dedicated "admin" db, place all the code you need in there and then use something like satya suggested?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  5. TurnerBurn New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />Invoke the SP(s) using dynamic SQL like so:<pre id="code"><font face="courier" size="2" id="code"><br /> USE master<br /> GO<br /> DECLARE @dbname VARCHAR(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /> SET @SomeOtherDb = 'test1'<br /> EXEC ('EXEC ' + @dbname + '.dbo.SP1')</font id="code"></pre id="code"><br /><br /><br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Thanks Satya. I'm just now getting back to this. I'm not sure I understand this compleatly. I think I can follow all but the SET @SomeOtherDb = 'test1' line. Not sure what that is. Can you clarify? <br /><br />Sorry if I'm asking you to state the obvious, but I just want to be sure.<br /><br />Thanks,<br /><br />Karl
  6. Madhivanan Moderator

    Instead of using USE,
    use DBname..ObjectName
    This will work inside stored Procedure

    Madhivanan

    Failing to plan is Planning to fail
  7. Madhivanan Moderator

    >>I think I can follow all but the SET @SomeOtherDb = 'test1' line. Not sure what that is. Can you clarify?

    It was given as an example to use Database Name dynamically
    Replace test1 by your DBName

    Madhivanan

    Failing to plan is Planning to fail
  8. FrankKalis Moderator

    <pre id="code"><font face="courier" size="2" id="code"><br /> USE master<br /> GO<br /> DECLARE @dbname VARCHAR(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /> SET @SomeOtherDb = 'test1'<br /> EXEC ('EXEC ' + @dbname + '.dbo.SP1')<br /></font id="code"></pre id="code"><br />When you split this into pieces, you see that first you build a string that looks like<br /><pre id="code"><font face="courier" size="2" id="code"><br />'EXEC test1.dbo.SP1'<br /></font id="code"></pre id="code"><br />and then you execute it. For dynamic SQL I always like to point folks to this source:<br /<a target="_blank" href=http://www.sommarskog.se/dynamic_sql.html>http://www.sommarskog.se/dynamic_sql.html</a><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  9. satya Moderator

    THanks for you addups Adriaan and Frank, it corrected in some other way.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. FrankKalis Moderator

    Btw, you can also use "USE your_db" inside dynamic SQL as long as it is on its own line of code.<br /><pre id="code"><font face="courier" size="2" id="code"><br />USE master<br />DECLARE @stmt NVARCHAR(100)<br />SET @stmt = 'USE Pubs ' + CHAR(10)<br /> + 'SELECT * FROM authors'<br />EXEC sp_ExecuteSQL @stmt <br /></font id="code"></pre id="code"><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  11. FrankKalis Moderator

    Correction!

    quote:
    Btw, you can also use "USE your_db" inside dynamic SQL as long as it is on its own line of code.
    Obviously, it also works without CHAR(10).

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  12. Madhivanan Moderator

    Yes
    Dynamic SQL doesnot need newlines

    Exec('Use DBName Select * from tableName') will also work

    Madhivanan

    Failing to plan is Planning to fail

Share This Page