current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have several link server on my server (about 20 servers). on these server exists a similar database and I want to execute a stored procedure on these server in depend of a parameter on my stored procedure on my server. I have following query for this idea. 

Create procedure test
    (
       @ServerName VarChar(100),
       @Code int
    )
As
    IF(@ServerName = 'Server1')Begin
       Exec Server1.SGDB.dbo.DocInsert @Code
    End Else IF(@ServerName = 'Server2')Begin
       Exec Server2.SGDB.dbo.DocInsert @Code
    End Else IF(@ServerName = 'Server3')Begin
       Exec Server3.SGDB.dbo.DocInsert @Code
    .
    .
    .
    End Else IF(@ServerName = 'Server20')Begin
       Exec Server20.SGDB.dbo.DocInsert @Code
    End
End

Can I have a dynamic query with @ServerName parameter without using exec(string command).

thanks in advance.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

You can replace the body of your stored procedure with this dynamic SQL:

declare @sql nvarchar(max)

set @sql = N'Exec ' + @ServerName + N'.SGDB.dbo.DocInsert @Code'

exec sp_executesql  @stmt = @sql, @params = N'@Code int', @Code = @Code

It will be also better to declare the parameter @ServerName as NVarChar(128) to always work on Unicode characters and to allow for the maximum length of a linked server name.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.