Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

"USE DATABASE" in stored procedure workaround? Expand / Collapse
Author
Message
Posted Tuesday, August 5, 2008 8:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 27, 2009 1:14 AM
Points: 47, Visits: 104
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




Post #546756
Posted Tuesday, August 5, 2008 8:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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
Post #546785
Posted Tuesday, August 5, 2008 9:06 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 20,727, Visits: 32,485
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #546807
Posted Tuesday, August 5, 2008 1:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 27, 2009 1:14 AM
Points: 47, Visits: 104
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
Post #547047
Posted Tuesday, August 5, 2008 2:08 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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".


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #547082
Posted Tuesday, August 5, 2008 2:37 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #547109
Posted Tuesday, August 5, 2008 2:46 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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...


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #547117
Posted Tuesday, August 5, 2008 2:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 20,727, Visits: 32,485
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #547119
Posted Tuesday, August 5, 2008 2:58 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #547127
Posted Tuesday, August 5, 2008 3:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 27, 2009 1:14 AM
Points: 47, Visits: 104
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
Post #547129
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse