CODEWITHSUNDEEP
sql-serverstored-procedures
Aviral Goyal
Aviral Goyal

Reputation: 45

Run dynamic SQL Server stored procedure with linked server

This is a dynamic stored procedure that will pass the database, linked server and state. When executing this stored procedure, it runs the stored procedure on the database on the linked server and gives the results back.

Working code - here the linked server is absolute and not passed as a variable

EXECUTE MYPROC 'CA','MYDB'

CREATE PROCEDURE [dbo].[MYPROC]
(
    @state varchar(2),
    @DATABASE char(20)
)
AS
    DECLARE @SQL @VARCHAR(MAX)
 
    SELECT @SQL = 'use ' + @DATABASE + ';
    SELECT * FROM pubs.dbo.authors WHERE state = @state'

    EXEC MYLINKSERVER.master.dbo.sp_executesql
              @SQL, N'@state char(2)', @state

Not working code: here the linked server is passed through a variable.

I get a "Syntax error" at @LINKEDSERVER**.**master

EXECUTE MYPROC 'CA','MYDB','MYLINKSERVER'

CREATE PROCEDURE [dbo].[MYPROC]
(
    @state varchar(2),
    @DATABASE char(20),
    @LINKEDSERVER VARCHAR(20)
)
AS
    DECLARE @SQL @VARCHAR(MAX)

    SELECT @SQL = 'use ' + @DATABASE + ';
    SELECT * FROM pubs.dbo.authors WHERE state = @state'

    EXEC @LINKEDSERVER.master.dbo.sp_executesql
            @SQL, N'@state char(2)', @state

Thanks in advance

Upvotes: 0

Views: 4649

Answers (2)

Weihui Guo
Weihui Guo

Reputation: 3997

I prefer to use OPENQUERY as it is usually much faster than using four-part query. So instead of SELECT * FROM [MYLINKSERVER].[MYDB].[dbo].[authors] WHERE [state] = 'CA';, try this:

SELECT * FROM OPENQUERY([MYLINKSERVER], '
    SELECT * FROM [MYDB].[dbo].[authors] WHERE [state] = ''CA''
')

And your procedure will be something like this

CREATE PROCEDURE [dbo].[MYPROC]
(
    @state CHAR(2),
    @DATABASE VARCHAR(20),
    @LINKEDSERVER VARCHAR(20)
)
AS

DECLARE @SQL NVARCHAR(500)

SET @SQL = N'SELECT * FROM OPENQUERY(' + QUOTENAME(@LINKEDSERVER) + ', ''
    SELECT * FROM ' + QUOTENAME(@DATABASE) + '.dbo.authors WHERE state = ''''' + @state + '''''
'')'
EXEC SP_EXECUTESQL @SQL
--PRINT @SQL -- To see the final query to execute

Or you can use FORMATMESSAGE as the answer given by Critical Error.

SET @SQL = FORMATMESSAGE ('SELECT * FROM OPENQUERY([%s], ''
    SELECT * FROM [%s].[dbo].[authors] WHERE [state] = ''''%s'''''');', QUOTENAME(@LINKEDSERVER), QUOTENAME(@DATABASE), @state
);
EXEC (@SQL);

Use QUOTENAME to avoid SQL injection. As the other parameter is limited to char(2), I guess it should be safe.

Upvotes: 0

critical_error
critical_error

Reputation: 6706

Try this in your SP:

DECLARE @SQL VARCHAR(MAX);
SET @SQL = FORMATMESSAGE ( 'SELECT * FROM [%s].[%s].[dbo].[authors] WHERE [state] = ''%s'';', @LINKEDSERVER, @DATABASE, @state );
EXEC ( @SQL );

This would create the following SQL statement to be executed based on your sample parameters above:

SELECT * FROM [MYLINKSERVER].[MYDB].[dbo].[authors] WHERE [state] = 'CA';

I'm not sure what version of SQL Server you're running, so you may not be able to use FORMATMESSAGE, however, I'm sure you're familiar with concatenating strings.

Upvotes: 1

Related Questions