How to have database name as variable in an SP?

Question

We use stored procedures exclusively here, and that raises a little problem. We cross reference two different databases like dbdev..table1 in dev, dbqa..table1 in qa, and dbprod..table1 in production.

So every time we deploy to a different environment, we have to search and replace from dbdev to dbqa or dbprod.

Is there a way to use synonym or whatever sql server mechanics to solve problem?

Answer 1

No.

It is not possible to create a Synonym for a database. That is a popular request though.

Is it really necessary to rename your databases for dbdev, dbqa, dbprod etc. though?

Answer 2

Dynamic sql

(forgive potential typos, but the concept is there)

Declare @dbname nvarchar(255), @sql nvarchar(max)
set @dbname = 'db1'

Set @sql = 'Select * From ' + @dbname + '.dbo.table1'
exec sp_executesql @sql

Answer 3

SQL Server 2005 supports synonyms, so you can create synonym1 to refer to dbdev..table1 in dev environment, and to dbprod..table1 in prod environment. Your SP's (and probably views) just operate on the synonyms.

Update:

The easiest way to create synonyms:

exec sys.sp_MSforeachtable 
    'print ''CREATE SYNONYM '' + REPLACE(''?'', ''].['', ''].[syn_'') + 
    '' FOR [my_database].?
    GO'''

(there is a line break before GO)

Run and paste result into new query window.

Answer 4

You can have the database name as a parameter of your stored procedure, then use Dynamic SQL to construct your queries.

Ex:

CREATE PROC MyStoredProcedure @DBName VARCHAR(50) 
AS

   DECLARE @SQL VARCHAR(MAX)
   SET @SQL = 'SELECT * FROM ' + @DBName + '.dbo.table1'
   EXEC sp_executesql @SQL

Then you would simply call your stored procedure with the appropriate DB Name:

EXEC MyStoredProcedure 'dbdev'

Answer 5

Use sqlcmd variables, which are supported by sqlcmd deployment of .sql provisioning scripts,a s well as by VSDB projects. So your provisioning script looks like:

create procedure usp_myProc
as
select .. from [$(crossdb)]..table1;
go

When deploying it in production you run sqlcmd /E /I provisoning.sql /v crossdb=dbprod, while the QA deployment will be done via sqlcmd /E /I provisioning.sql /v crossdb=dbqa. See Using sqlcmd with Scripting Variables.

As a side note, I am working on a project that allows sqlcmd variables to be used from .Net SqlClient (SqlConnection, SqlCommand): the dbutilsqlcmd project.