parameters for dbname in sp's

We have several stored procs which will be reuesed through client db's where there dbnames will be different for example

select m.id, f.id from maryland.dbo.table inner join f.dbo.table

If I want to create a parm that is changed at the beginning of the code for the Maryland in this case what are either the best ways to do this (including for performance) or reasons to avoid, I have heard perf arguments against doing this.

HaywoodNew Member

16 Nov 2007 01:18 PM

<Insert Standard Dynamic SQL Security Warning>

Dynamic SQL being called from a webserver is bad, mmmmkay.

And what Russell said.

timcroninNew Member

19 Nov 2007 06:32 AM

OK, let me clear things up a bit. The db's referenced will all be on the same server. However the dbnames will not be the same (named after state of client)

timcroninNew Member

19 Nov 2007 12:44 PM

SP's will reside in a single database. These db's will have different client names so different dbnames. My basic question is whether or not declaring a variable for the dbname that will be reference in sp hurst performance

HaywoodNew Member

20 Nov 2007 05:19 AM

This script is a great demonstration of what the OP is looking for, IMO.

It's designed to be called from a singular database called "Admin" and will operate on any database on the server.

./Shameless Plug

HaywoodNew Member

21 Nov 2007 05:35 AM

I have a couple of procedures I wrote that I've done this with (marksystemobject). It's been a while since I've written them, but IIRC, I did run into certain functions/operations that by having it in master and operable, it still didnt solve the design problem at hand. More often than not, I wind up using dynamic sql...

This trick does work well though for some things. But, yes, it is undocumented and you have to be cognisant that.