Can a database name be called using a variable?

Question

Post reply

Can a database name be called using a variable?

FdoArens

SSC Enthusiast

Points: 196
More actions
April 19, 2005 at 12:25 pm

#66946

Thank you for your help. I need to recreate a lot of stored procs to a different server and database, I was thinking of modifying my store procs by adding a variable to call a specific database, can this be done? If so, how? My stored procs looks like this:
CREATE PROCEDURE usp_ETLCommission_Plan
AS
-- Replacing with query which brings binary id from table where binary id is the key as opposed to straight
--from the extracted and transformed construction stage table.
--SELECT
-- *
--FROM
-- ETLBD_Commission_Plan
SELECT
BCP.BD_Begin_Date,
BCP.BD_Default_Plan,
BCP.BD_End_Date,
BCP.BD_Plan_Name,
BCP.BD_Plan_Type,
BCP.ETLBD_Commission_Plan_Id,
ED.PivotalDivision_Id AS BD_Project_Id
FROM
PivotalTLStaging.dbo.ETLBD_Commission_Plan BCP WITH (NOLOCK)
INNER JOIN PivotalTLStaging.dbo.ETLDivision ED WITH (NOLOCK)
ON BCP.ETLBD_Project_Id = ED.ETLDivisionID

GO
Where the call to the database is being done here PivotalTLStaging.dbo.ETLBD_Commission_Plan and send the record set to a different database
Thank you for your assistance.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Will this help?

DECLARE @stmt NVARCHAR(1000)

SET @stmt = 'USE NORTHWIND' + CHAR(10)+'SELECT * FROM Orders'

EXEC sp_executeSQL @stmt

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

FdoArens SSC Enthusiast Points: 196 More actions · Answer 2

Thanks Frank, I will try. What I don't is to hard code the database name to allow any of my stored procedures to run regardless of the database to be used. I think your approach could help because the change will be only in one section of the store proc. I will give it a try. Thanks again

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Why don't you just leave the dbname out?

There's no point, and not preferred either, to reference tables with dbname.owner.objectname if it's not intended to be a crossdatabase query in the first place.

In your example, just change

PivotalTLStaging.dbo.ETLBD_Commission_Plan BCP WITH (NOLOCK)

INNER JOIN PivotalTLStaging.dbo.ETLDivision ED WITH (NOLOCK)

to

dbo.ETLBD_Commission_Plan BCP WITH (NOLOCK)

INNER JOIN dbo.ETLDivision ED WITH (NOLOCK)

and you're done...

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Doh, I thought he was after dynamically switching db context...

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

Well... re-reading the original post (last paragraph) I realize that I'm not entirely sure either what the real question is about....

I'm probably lost as well..

/Kenneth

FdoArens SSC Enthusiast Points: 196 More actions · Answer 6

Sorry guys for not being clear enough. What happen is that the stored procs are use in a data base, but insert data into a different one. That is why I thought of getting a db variable and run them from that database or any other and not restric myself to the same database and change the database name each environment. We have three diffrent servers and the develores use different names for the DBs (why? please don't ask!!! LOL) SO, I ended up creating the stored procs for the first environment without realizing those three other environments. That is the reason behind this, other wise I will just create the stored procs in one database and that will do it. Dinamically run them all.

I think Frank's solution is going to work. Not sure yet, but it worked for a couple!! Cool!!

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 7

Another solution is to use a led pipe and apply it to the devs fingers.

If they can't begin being consistent with names even, how can you then trust the stuff they actually 'develop'..?

well... no offense to devs, really - but what is a fact, (imo anyway) is that it's way to much code out there with the sole purpose of 'working around bad/lazy habits'

/Kenneth

FdoArens SSC Enthusiast Points: 196 More actions · Answer 8

You are absolutely correct, no disrespect but sometimes .... Enough of that. It seems, so far, that the solution given by Frank worked for them. I talk to one of the devs and he said, that it could be the solution for all their problems. I hope it does and I hope there is no more headaches!! Fat chance, but dream doesn't cost a cent!! Thanks for all your help.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 9

To be honest, when devs say that a dynamic solution is the 'solution for all their problems' I literally get chills down my spine.

Before you let them play with this any further, please read up on what, why and how this really works... and what the cost you're about to pay for it may be..

http://www.sommarskog.se/ (especially the one titled 'The Curse and Blessings of Dynamic SQL')

/Kenneth

FdoArens SSC Enthusiast Points: 196 More actions · Answer 10

Thanks, I know, I know and that's exactly what I am doing today!!

noeld SSC Guru Points: 96590 More actions · Answer 11

There is another way to solve this!

Declare @dbname1 varchar(20), @dbname2 varchar(20), @spname varchar(128), @retval int

select @dbname1 = 'FirstDBNAME', @dbname2 ='SECONDDBNAME'

-- call proc on db1

set @spname = @dbname1 +'.dbo.procedurename'

exec @retval = @spname

-- call proc on db2

set @spname = @dbname2 +'.dbo.procedurename'

exec @retval = @spname

You could even pass parameter like

-- exec @retval = @spname, @parm1, @param2

hth

* Noel