How can I get dbX.dbo.procA to run in the context of dbY?

Question

I want to separate a specific set of stored procedures into a seperate database … sort of like a library. However, when I run these procs from another database, they appear to call objects from within the library database.

Here is some source code which demonstrates the problem

use mylibdb
go

create proc gettablecount
as
begin
    declare @cnt int;
    select @cnt=count(*)
    from sysobjects
    where xtype='U';
    print 'Table count : ' + cast( @cnt as nvarchar);
end
go

use adventureworks
go

exec mylibdb.dbo.gettablecount;

print '';
select  count(*) as [table count]
from    sysobjects
where   xtype='U';

Running that code will print out

Table count : 0

table count

---

71

(1 row(s) affected)

Notice the proc queries the mylibdb.dbo.sysobjects table, not adventureworks.dbo.sysobjects.

Does anybody know how I can do this without dynamic sql?

Answer 1

You could treat the library as code instead of database objects and create the procedures in the database schemas that use the "library". I would do this by storing your procedures and other shared db objects as CREATE scripts that you can modify and version.

Answer 2

dynamic sql is your safest bet, your other option is to create the proc in the model database and it will be available in every database you create after that (but not in the current ones so you will have to add the proc to those)

Answer 3

I've determined this cannot be done, so I saved the scripts as script files, rather than in procs.

So, my proc would be reduced to this anonymous block:

begin
    declare @cnt int;
    select      @cnt=count(*)
    from        sysobjects
    where       xtype='U';
    print       'Table count : ' + cast( @cnt as nvarchar);
end

PS-This is just a 'For The Record' type answer.

Answer 4

An alternative is to create sp_ procedures in the master database:

http://www.mssqltips.com/tip.asp?tip=1612