"USE DATABASE" in stored procedure workaround?

  • Hi,

    I have 2 customers; they both have their own database that is identical to the other. What I want is a "common" database that holds the stored procedures. Something like this pseudocode:

    DATABASE Cust1

    Stored Procedure GetEmployees(@town)

    BEGIN

    --- USE Cust1

    --- EXEC common.dbo.GetEmployees(@town)

    END

    DATABASE Cust2

    Stored Procedure GetEmployees(@town)

    BEGIN

    --- USE Cust2

    --- EXEC common.dbo.GetEmployees(@town)

    END

    DATABASE common

    Stored Procedure GetEmployees(@town)

    BEGIN

    --- SELECT * FROM employees WHERE town=@town

    END

    Both customer 1 and customer 2 call the same GetEmployees stored procedure in the common database; obviously they each get their own list of employees in the given town.

    It is not possible to use USE DATABASE in a stored procedure, I don't know how to work around this. In the real world I don't have 2 customers, but much, much more.

    Thanks,

    Raymond

  • One solution is to add the stored procedures to the master database, and add sp_ prefix to their names

    (bad practise)

    Another solution is to use a tool to sync the stored procedures between the various databases.

    Third solution is to use dynamic sql and use three part names like customerdb1.dbo.sometable

    None of the above solutions (maybe apart from the second) are nice though.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Andras Belokosztolszki (8/5/2008)


    One solution is to add the stored procedures to the master database, and add sp_ prefix to their names

    (bad practise)

    Another solution is to use a tool to sync the stored procedures between the various databases.

    Third solution is to use dynamic sql and use three part names like customerdb1.dbo.sometable

    None of the above solutions (maybe apart from the second) are nice though.

    Regards,

    Andras

    Actually, I don't think it is a bad practice to create a stored proc that resides in master. You just have to be careful in naming the proc so that you don't have to worry about Microsoft coming out with a system proc of the same name. A bad practice is naming procs with sp_ that reside in a user database, as SQL Server will first look to master then the user database.

    😎

  • Hi there,

    Thanks for your input.... however being a newbie about where (or where not) to place SP's I'm not quite getting it. I understand that when you put a SP in the master database, the name should begin with sp_ , and that a name should be chosen that Microsoft will not likely use in the future.

    Question (1)

    I assume I can put any own SP in the master database Programmability|Stored Procedures... or should I put it in Programmability|Stored Procedures | System Stored Procedures ?

    Question (2)

    Will any SP in the master database use data in the user-database it is fired from? In my first test I could fire a SP named sp_mytest in the master database from a user-defined database; but I 'm not getting a resultset back (SELECT * FROM customers gives the error:Invalid object name 'customers')

    This SP does work:

    declare @dbname sysname

    SET @dbname = db_name()

    exec('SELECT * from '+@dbname+'.dbo.customers')

    ... but then I would have to rewrite all queries to a querystring.... not acceptable

    Question (3)

    Apart from the SP's name having to be unique, are there any other considerations?

    Looking forward to any input!!

    Thanks, Raymond

  • Andras Belokosztolszki (8/5/2008)


    One solution is to add the stored procedures to the master database, and add sp_ prefix to their names

    (bad practise)

    Well, this works in the sense that SP_FOO will find the proc in master.dbo, no matter what database you are in. However, it does NOT work in the sense that it will act like it is running in your current database. Like all other stored procedures (except the MS supplied Master.sys procs) Master.dbo.sp_* procs are complied and bound against the database that they are stored in, as the following demonstrates:

    USE MASTER

    Go

    create proc sp_RBY_check_db_loc

    as

    Select distinct TABLE_CATALOG

    From INFORMATION_SCHEMA.TABLES

    Go

    exec sp_RBY_check_db_loc

    Select distinct TABLE_CATALOG

    From INFORMATION_SCHEMA.TABLES

    Go

    USE model

    Go

    exec sp_RBY_check_db_loc

    Select distinct TABLE_CATALOG

    From INFORMATION_SCHEMA.TABLES

    Go

    As you will note, no matter which DB you USE, the "exec" always returns "master".

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • R. van Laake (8/5/2008)


    Hi there,

    Thanks for your input.... however being a newbie about where (or where not) to place SP's I'm not quite getting it. I understand that when you put a SP in the master database, the name should begin with sp_ , and that a name should be chosen that Microsoft will not likely use in the future.

    Question (1)

    I assume I can put any own SP in the master database Programmability|Stored Procedures... or should I put it in Programmability|Stored Procedures | System Stored Procedures ?

    Question (2)

    Will any SP in the master database use data in the user-database it is fired from? In my first test I could fire a SP named sp_mytest in the master database from a user-defined database; but I 'm not getting a resultset back (SELECT * FROM customers gives the error:Invalid object name 'customers')

    This SP does work:

    declare @dbname sysname

    SET @dbname = db_name()

    exec('SELECT * from '+@dbname+'.dbo.customers')

    ... but then I would have to rewrite all queries to a querystring.... not acceptable

    Question (3)

    Apart from the SP's name having to be unique, are there any other considerations?

    Looking forward to any input!!

    Thanks, Raymond

    One more consideration for placing stored procedures into master - and that is, you need to mark them as system stored procedures. Otherwise, they just run in the context of the master database.

    You would do this:

    Execute sp_MS_marksystemobject sp_mytest;

    And that should setup the procedure so it runs in the context of the database where it is executed.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams (8/5/2008)


    You would do this:

    Execute sp_MS_marksystemobject sp_mytest;

    And that should setup the procedure so it runs in the context of the database where it is executed.

    Very nice, Jeffrey! I have been wondering about this for a while...

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • rbarryyoung (8/5/2008)


    Andras Belokosztolszki (8/5/2008)


    One solution is to add the stored procedures to the master database, and add sp_ prefix to their names

    (bad practise)

    Well, this works in the sense that SP_FOO will find the proc in master.dbo, no matter what database you are in. However, it does NOT work in the sense that it will act like it is running in your current database. Like all other stored procedures (except the MS supplied Master.sys procs) Master.dbo.sp_* procs are complied and bound against the database that they are stored in, as the following demonstrates:

    USE MASTER

    Go

    create proc sp_RBY_check_db_loc

    as

    Select distinct TABLE_CATALOG

    From INFORMATION_SCHEMA.TABLES

    Go

    exec sp_RBY_check_db_loc

    Select distinct TABLE_CATALOG

    From INFORMATION_SCHEMA.TABLES

    Go

    USE model

    Go

    exec sp_RBY_check_db_loc

    Select distinct TABLE_CATALOG

    From INFORMATION_SCHEMA.TABLES

    Go

    As you will note, no matter which DB you USE, the "exec" always returns "master".

    I think, in this particular case, it has to do with the INFORMATION_SCHEMA views. I played with them in SQL Server 2000, and for those to work correctly, you had to be in the database (USE [dbname_here]) for them to work. I have written a couple of generic procedures where I work and put them in the master database and they work okay.

    😎

  • rbarryyoung (8/5/2008)


    Jeffrey Williams (8/5/2008)


    You would do this:

    Execute sp_MS_marksystemobject sp_mytest;

    And that should setup the procedure so it runs in the context of the database where it is executed.

    Very nice, Jeffrey! I have been wondering about this for a while...

    I should have also mentioned that this little procedure is undocumented. But, it has been around for a while and I don't see MS getting rid of it. Anyways, use at your own risk. 😉

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Hi Jeffrey (and others)

    Thanks! Exactly what I needed!

    I stumbled across

    http://msdn.microsoft.com/en-us/library/ms187760.aspx

    that handles synonyms, it was not perfect at all, but better than nothing.... of course your solution is better.

    Of course sp_MS_marksystemobject being undocumented worries me.... any mention of it still being in SQL2008?

    Thanks a lot again!!!!!!!!!

    Raymond

  • From: http://www.webtropy.com/articles/SQL.asp?SQL=sp_MS_marksystemobject

    -- FOR INTERNAL USE ONLY ... DO NOT DOCUMENT --

    -- This procedure sets a bit in sysobjects. This bit has no meaning, various

    --groups (starfigther, davinci, replication) use it for different things

    -- MSQL makes no warranty, express or implied, on what objects will or will

    --not have this bit set. Use at your own risk.

    --

    create procedure sp_MS_marksystemobject

    @objname nvarchar(517) -- 517 is max for two part name

    as

    -- pre-stuff --

    set nocount on

    -- CHECK THE OBJECT NAME --

    if object_id(@objname, 'local') is null

    begin

    raiserror('sp_MS_marksystemobject: Invalid object name ''%ls''',0,1,@objname)

    return 1

    end

    -- CHECK THE OBJECT OWNER (MUST BE A SYSTEM USER) --

    if user_name(ObjectProperty(object_id(@objname, 'local'), 'ownerid'))

    not in ('dbo','INFORMATION_SCHEMA')

    begin

    raiserror('sp_MS_marksystemobject: Object must be owned by a system user.',0,1)

    return 1

    end

    -- DO THE UPDATE --

    begin tran

    dbcc LockObjectSchema(@objname)

    update sysobjects set status = status | 0xC0000000

    where id = object_id(@objname, 'local')

    commit tran

    return @@error -- sp_MS_marksystemobject

  • Lynn Pettis (8/5/2008)I think, in this particular case, it has to do with the INFORMATION_SCHEMA views. I played with them in SQL Server 2000, and for those to work correctly, you had to be in the database (USE [dbname_here]) for them to work. I have written a couple of generic procedures where I work and put them in the master database and they work okay.

    😎

    They fixed that behavior of INFORMATION_SCHEMA views in SQL2005.

    Anyway, the solution is the undocumented sproc "sp_MS_marksystemobject" that Jeffrey posted. If you use that, then my same test will give the desired results. Very cool!

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • rbarryyoung (8/5/2008)


    Lynn Pettis (8/5/2008)I think, in this particular case, it has to do with the INFORMATION_SCHEMA views. I played with them in SQL Server 2000, and for those to work correctly, you had to be in the database (USE [dbname_here]) for them to work. I have written a couple of generic procedures where I work and put them in the master database and they work okay.

    😎

    They fixed that behavior of INFORMATION_SCHEMA views in SQL2005.

    Anyway, the solution is the undocumented sproc "sp_MS_marksystemobject" that Jeffrey posted. If you use that, then my same test will give the desired results. Very cool!

    Okay. I haven't used the INFORMATION_SCHEMA views in SQL 2005. When I have needed info, I have used the sys tables, even if I shouldn't be, it has just been easier; plus I have been able to get additional info from the tables for other purposes.

    😎

Viewing 13 posts - 1 through 12 (of 12 total)

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