Variable for DB name 1

[MDA]

Hi all,

Simple question...

I have a stored procedure-- I want to be able to change the database I copy data from and to. Below is a simple example... I cannot figure out why I am unable to use a variable for the database name....

declare @DBname varchar(255)
select @DBname = 'db2'

select accoID from @DBname.dbo.acco


If I type db2.dbo.acco it works perfect. However, when I use the variable I get the error:

Incorrect syntax near '.'

Any ideas are greatly appreciated...

Regards,

Mike

 

[tlbroadbent]

Create a dynamic SQL statement and execute it.

declare @sql varchar(1024)
declare @DBname varchar(255)

Select @DBname = 'db2'
Set @sql='Select accoID from ' + @DBname + '.dbo.acco'

exec sp_executesql @sql Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[MDA]

Thanks for your help Terry.... I think this is not quite what I am looking for... This stored procedure is very large and creates cursors, etc, etc..

I would like to avoid changing the 120+ lines of T-SQL in this SP.... Is there any other way to simply have a variable for the database name??

I appreciate your time... any other ideas??

Thanks,

Mike

 

[tlbroadbent]

You can take advantage of system stored procedures. [ol][li]Create your stored procedure in the master database. The name must start with 'sp_' such as 'sp_CopyMyData' in order to be considered a system stored procedure.

[li]Modify the stored procedure to remove the database parameter.

[li]Remove the database parameter from the table references.

Example: Select accoID from dbo.acco

[li]Execute the stored procedure qualifying the name with the name of the database you wish to process.

Example: Exec db2.dbo.sp_CopyMyData[/ol]The procedure will execute in the context of the named database. Hope this helps. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[MDA]

Hey Terry,

Thanks again for your time... I think the challenge is that I am dealing with two databases. You see, the SP must copy transactions from one database, put them in a cursor, then insert them into another database. So I am not sure if that method would work, as I am dealing with two seperate databases.

Thanks again for your input.

Mike