Link to home
Start Free TrialLog in
Avatar of AlizaN
AlizaN

asked on

creating one central sp for multiple databases

Hi.  This is a  "related question" about a situation as follows:
I have multiple databases with identical structures.  I want to have one central stored procedure that can be used in any of my databases. What I would really want to do is have one sp and in it say "use DB1" or "use DB2" based on a parameter or something.  But the use statement cannot be used in an sp.

There is already a (Very long) post about this.  I am interested in this answer from creeds0001::
"... All   of that being said, put the stored porcedure in the MASTER database.  If you put them anywhere else calling them will cause them to use the data in their resident database.  The MASTER database is different.  You might want to give them some special kind of name to help keep track of them, but this will allow you to call the procedure useing your current databases data."

I don't understand how this works.  Based on the above post, I tried the following:  

create procedure MasterTestSP as
select * from mytable

I created the procedure MasterTestSP in the Master Database. Master db does not have  a  table "mytable".  
I went into one of the user databases, StandardDB which has a table "mytable" and ran
exec master.dbo.MasterTestSP

I got the message "Invalid object name mytable".
Which makes sense because it's looking at the master database, not at the child database.

So basically, I don't understand the post. How do I store one central sp in Master and point it to the calling database at runtime?

Thanks.

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AlizaN
AlizaN

ASKER

use master
create procedure MasterTestSP as
select * from mytable

exec sys.sp_ms_marksystemobject mastertestsp

use simpledb
exec mastertestsp
"could not find stored procedure MasterTestSP"

exec master.dbo.mastertestsp
"invalid object name mytable"

what am i doing wrong? did i set it up correctly? how do i call it?

Avatar of AlizaN

ASKER

ok i got it you have to preface it with sp_ for some reason
sp_mastertestsp works
but
mastertestsp doesn't.

thanks so much, this is really terrific!
>>ok i got it you have to preface it with sp_ for some reason <<
Right.  You have just defined it as a system Stored Procedure, so it has to use the prefix sp_