CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Rhushikesh
Rhushikesh

Reputation: 3700

How to use variable for server name in SQL Server stored procedure

I have two database servers, server1 and server2, which are our diff environment of our legacy system. I have to write a stored procedure for getting data which is communicate with one of the env.

I want a way so that I can easily switch the env

Like:

Declare @Server nvarchar(20)
set @Server="server1"

select * 
from @Server.global.dbo.tblaccount

Upvotes: 1

Views: 2312

Answers (2)

Paolo
Paolo

Reputation: 2254

use synonyms:

create synonym mylocalschema.RemoteTable for server1.database.schema.table;

to be referenced as:

select top 10 * from mylocalschema.RemoteTable;

depending on env the synonyms will be a reference to the right server and the code (sp, views, triggers, whatever) will be the very same for both.

the only duplicate item is the script to create synonyms: one for server1 and another copy for server2 (or maybe the same script built with dynamic sql...).

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Parameters cannot be used for identifiers -- column names, table names, schema names, server names, function names, and so on.

You can do this with dynamic SQL:

declare @Server nvarchar(20);
declare @sql nvarchar(max);

set @Server = 'server1';

set @sql = 'select * from [Server].global.dbo.tblaccount';

set @sql = replace(@sql, '[Server]', @Server);

exec sp_executesql @sql;

Upvotes: 3

Related Questions