none
How to send a linked server name as a parameter of stored procedure?

    Question

  • I'm trying to create a simple stored procedure which queries with Linked server.
        create procedure test
        @LinkedServeName nvarchar(20)
        as
        begin
          select * from @LinkedServerName.WorkersDB.tbl_workers
        end
    I know about dynamic SQL but I'm looking for the other solution.
    Sunday, August 28, 2011 4:38 AM

Answers

  • Raymond you would have to create some dynamic sql in your stored proc unfortunately there is no other option for you to concatenate a variable into a From clause

     

    Create procedure Test @LinkedServerName nVarchar(20)
    As
    Begin
    Declare @Cmd nVarchar(255)
    
    Set @Cmd = 'Select * from ' + @LinkedServerName + '.WorkersDB.dbo.tbl_workers'
    
    Exec sp_executeSQL @Cmd
    End
    


    Warwick Rudd MCT MCITP SQL Server 2008 Admin
    Sunday, August 28, 2011 4:52 AM

All replies

  • Raymond you would have to create some dynamic sql in your stored proc unfortunately there is no other option for you to concatenate a variable into a From clause

     

    Create procedure Test @LinkedServerName nVarchar(20)
    As
    Begin
    Declare @Cmd nVarchar(255)
    
    Set @Cmd = 'Select * from ' + @LinkedServerName + '.WorkersDB.dbo.tbl_workers'
    
    Exec sp_executeSQL @Cmd
    End
    


    Warwick Rudd MCT MCITP SQL Server 2008 Admin
    Sunday, August 28, 2011 4:52 AM
  • One alternative would be to have a separate query for each possible linked server, and use IF statements to evaluate @LinkedServerName to determine which query to run:

     

     

    IF (@LinkedServerName = 'Server1') 
    BEGIN
    	SELECT * FROM Server1.WorkersDB.dbo.tbl_workers
    END
    ELSE IF (@LinkedServerName = 'Server2')
    BEGIN
    	SELECT * FROM Server2.WorkersDB.dbo.tbl_workers
    END
    ...
    
    

    This takes a lot more work depending on how many servers there are, and would not support new servers without future modifications, but it would solve the problem without dynamic sql if that is truly a requirement.

     

     

     


    Sunday, August 28, 2011 3:10 PM