parameters for dbname in sp's

Last Post 20 Nov 2007 06:19 AM by Haywood. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
timcronin
New Member
New Member

--
15 Nov 2007 11:56 AM
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.
Haywood
New Member
New Member

--
16 Nov 2007 02:18 PM
<Insert Standard Dynamic SQL Security Warning>

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



And what Russell said.
timcronin
New Member
New Member

--
19 Nov 2007 07: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)
timcronin
New Member
New Member

--
19 Nov 2007 01: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
Haywood
New Member
New Member

--
20 Nov 2007 06: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

Haywood
New Member
New Member

--
21 Nov 2007 06: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.
Haywood
New Member
New Member

--
21 Nov 2007 08:33 AM
I haven't tested 2008 yet, but it still works in 2005.
You are not authorized to post a reply.

Acceptable Use Policy