CODEWITHSUNDEEP
sql-serverdatabase
Haoest
Haoest

Reputation: 13896

How to have database name as variable in an SP?

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?

Upvotes: 0

Views: 3357

Answers (5)

devio
devio

Reputation: 37205

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.

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294187

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.

Upvotes: 3

bv8z
bv8z

Reputation: 975

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'

Upvotes: 0

Jeremy
Jeremy

Reputation: 4838

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

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 452957

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?

Upvotes: 1

Related Questions