Set server and database as parameters in stored procedure

[hammer02]

I have some stored procedures that I would like to improve the maintenance efforts on. These stored procedures execute SQL statements from different databases on different servers. I would like to be able to make the stored procedures more generic so I don't have to change the server and database name in several places when changing between development and production environments. Below is a snippet of some of the code. The DEVSQL1 is the server name and TESTDB is the database.

insert into devsql1.testdb.dbo.audit_trail
(uptime, process_name, table_name, iud_ind, productiondate)
values (getdate(), 'int_transfer_DEV', 'production', 'x', @trans_date)

insert into devsql1.testdb.dbo.production
(date, ...

Is there anyway to define a parameter or value at the top of the stored procedures so the maintenance effort can be dramatically reduced?

Thanks,
MH

 

[Pattycake245]

You could pass these two parameters to your stored procedure. You would have to put your select statements into strings. Something like:

CREATE PROCEDURE whatever
@servername nvarchar(10), @database nvarchar(10),

AS

@SQLString nvarchar(100)

set @SQLString = N' insert into '+@servername+'.'+@database+'.dbo.audit_trail
(uptime, process_name, table_name, iud_ind, productiondate)
values (getdate(), 'int_transfer_DEV', 'production', 'x', @trans_date)

print @SQLString
exec sp_executesql @SQLString

--Then to run it...
exec whatever 'servername','database'

Tim