May 21, 2002 at 4:37 am
I want to be able to use a variable for the database name in a stored procedure,
eg
select * from @DBName..Sysusers
But this would return Invalid object name
The variable will be based on the current DB name but using replace to amend part of the name.
I don't want to use if clauses as it increases the size of the SP.
Can anyone help?
May 21, 2002 at 4:55 am
The only way to replace the DB Name is to use dynamic SQL.
quote:
The variable will be based on the current DB name but using replace to amend part of the name.
But this confuses me, are you saying you want to run this code against the current db you are in?
If so try this way.
CREATE PROC sp_getsysusers
AS
select * from Sysusers
In master
then call
EXEC dbname..sp_getsysusers
or as you stated will run in the current db then do
EXEC sp_getsysusers
for the current db connected to.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 05/21/2002 04:55:40 AM
May 21, 2002 at 5:16 am
Thanks for the answer, at
I should have given you a bit more info.
What I want to do is join to another table in another database in my SP, but this is a generic SP used in multiple db's and the name of the second db in the join is based on the main db
ie
The SP will run in DB called MainPENT
select....
from customers
left outer join PricingPENT..Bookings ....
We want to be able to copy the SP from db to db without having to edit it everytime!
May 21, 2002 at 6:18 am
What's wrong with modifying it? I have the unusual situation of having more than 200 db's that all have to have one proc modified when the db is created. I wrapped up that change along with some actions and combined into a small util app that does it all.
Andy
May 21, 2002 at 6:37 am
Otherwise you will have to do with dynamic sql like so
CREATE PROC theProc
@dbname varchar(255)
AS
SET NOCOUNT ON
DECLARE @SQLStr VARCHAR(2000)
SET @SQLStr = 'select....
from customers
left outer join ' + @dbname + '..Bookings ....'
EXEC (@SQLStr)
The major down side is you will not be able to take advantage of stored execution plans and loose a major bennifit of SPs.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 21, 2002 at 6:41 am
Andy,
I presume that you are the only one that looks after the db's then? I have a problem with developers or management transferring SP's from a Stage server to a live server, as well as new db's being created, 99% of the time this is not a problem, but every now and then someone makes some changes without realising that they should have amended things such as SP's, thus causing a number of headaches. Ideally we wouldn't allow them to do this but it is not practical to do so, negating as much as possible by having SP's work out the DB's they need to link to would be pretty much the final piece of the jigsaw in having a system that didn't need external intervention to stop these sort of problems from happening! Having a utility to amend the relevant code is a methodology I have used on numerous occasions, but it does need someone to run it and it can get forgotten at times, especially if I am not around!
It would seem that there is a big hole in SQL server if you can't define the DB to use in the way I want to, without using dynamic SQL. My other option is to build the SQL in the ASP code rather than using a SP, but this isn't the route I wanted to go.
Still thanks for your answers people....
May 21, 2002 at 7:07 am
I'll agree its not obvious that you can't do what you suggest, after all its done so easily in VB/ASP/etc. Still, thats the rules for now!
Im the DBA, but I don't create the db's. Didnt make sense for me to bottle neck the process and since I was going to can the whole process anyway (create db on two servers, set up replication, modify proc, bunch of other stuff) I added code to only let a couple specific users run the app, embedded a sql login that has sysadmin permissions with a password that has been obfuscated in the code. They can create any time they want, it's always done right, they don't have to wait on me.
One other thing. Not clear enough on your situation to know if it would work, but often the answer lies in recasting your problem. Maybe multiple db's is the wrong approach, would make more sense to add a "DB" column to your tables. Probably other approaches besides that one too.
Andy
May 21, 2002 at 7:28 am
Andy,
As the problem only relates to 2 SP's I'm thinking that the best route is to abandon SP's and build the SQL in the ASP code, thus the problem goes away.
By the way, I agree that two db's probably isn't the right approach, but I inherited them and its tooooo late now....
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply