CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Milan
Milan

Reputation: 3335

Stored procedure with @variable server name

I am trying to create a new stored procedure sp_sync_table (in SQL Server 2012) which accepts one parameter (linked server name). However jut declaring the @linkedServerName variable is not enough. The CREATE PROCEDURE directive fails because it cannot resolve the @linkedServerName unless @linkedServerName is SET to a specific name.

It fails with error:

Msg 102, Level 15, State 1, Procedure sp_sync_table, Line 31
Incorrect syntax near '.'.)

Obviously I cannot provide a specific name, that has to be passed in through the 

exec sp_sync_table server_name

This is the failing part:

CREATE  PROCEDURE sp_sync_table
    @linkedServerName AS VARCHAR(50)
AS BEGIN
.
.
.
DECLARE cursorX CURSOR FOR

SELECT col1, col2, col3, col4 FROM @linkedServerName.[db-name].[dbo].[table]

OPEN cursorX
.
.
.

What would be the workaround for this scenario ?

Upvotes: 0

Views: 1898

Answers (1)

macoms01
macoms01

Reputation: 1110

Could you use dynamic sql? I know it's not the safe way to go about doing it, but you could place your query in a string and execute the string. Just be super careful of what the user passes in for the @linkedServerName. Obviously you will be executing whatever you allow your user to enter. Not sure how this will work with a cursor though - I've never tried.

CREATE  PROCEDURE sp_sync_table

    @linkedServerName AS VARCHAR(50)

AS BEGIN

.
.
.
DECLARE queryToExecute varchar(max)
select  queryToExecute = 'DECLARE cursorX CURSOR FOR'
select queryToExecute = queryToExecute + 'SELECT col1, col2, col3, col4 FROM'
select queryToExecute = queryToExecute + ' ' + @linkedServerName + '.[db-name].[dbo].[table]'
select queryToExecute = queryToExecute + 'OPEN cursorX...'
exec(queryToExecute)

Upvotes: 1

Related Questions