I used to write database-agnostic stored procedures by creating them in master with the sp_ prefix and referring to dbo.sysobjects, which at runtime would bind to whatever database I happened to be in.
eventually I tried to migrate my code to use the newer, friendlier sys.tables, however, that binds to the database where the stored proc was compiled and thus returns the wrong data.
example:
use master
go
create proc sp_test
as
select name
from sys.tables
go
use MyOtherDb
go
exec sp_test
go
will yield a list of the tables in master NOT in MyOtherDb - consequently when writing this stuff now I'm obligated to wrap my code in a string and execute it dynamically, which is particularly difficult once I'm already a layer or two deep in code that writes code.
anyone have a solution to this problem? I'm sure I'm not the only guy out there that has bashed his head against this!
