Pass multiple parameters using sp_executesql

Question

I am using SQL Server 2016 to write a stored procedure with dynamic SQL and sp_executesql with multiple parameters.

CREATE PROCEDURE [dbo].[testsp] @ProductName nvarchar(250), @ProductDescription nvarchar(250)
AS

DECLARE @query NVARCHAR(MAX)

    SET @query = 'EXEC (''INSERT INTO [dbo].[Products] ([ProductName], [ProductDescription]) VALUES (?,?)'', @ProductName, @ProductDescription) AT [linkedserver]' 
  EXEC sp_executesql @sql, N'@ProductName nvarchar(250), @ProductDescription nvarchar(250)', @ProductName, @ProductName;

Error: Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'

I need to make a jdbc call of the stored procedure.

call [dbo].[testsp](?,?)

Also I need to get the name of the linked server dynamically. If the environment is DEV then the name of linked server would be [LinkedServer_DEV], TEST would be [LinkedServer_TEST]. — pbj, Apr 09 '20 at 15:04

Zhorov · Answer 1 · 2020-04-09T09:23:39.187

If I understand this question correctly, you need to define the stored procedure in the following way to use EXECUTE .. AT (using the ? syntax). Note, that [linkedserver] must refer to an existing linked server definition in the local server.

CREATE PROCEDURE [dbo].[testsp] 
    @ProductName nvarchar(250), 
    @ProductDescription nvarchar(250)
AS
BEGIN
    EXEC sp_addlinkedserver 'linkedserver', 'SQL Server'  

    DECLARE @err int

    EXECUTE (
        N'INSERT INTO [dbo].[Products] ([ProductName], [ProductDescription]) VALUES (?, ?)', 
        @ProductName, @ProductDescription
    ) AT [linkedserver]  

    SELECT @err = @@ERROR
    RETURN @err
END

If you want to use sp_executesql, you need to create the stored procedure using the following statement:

CREATE PROCEDURE [dbo].[testsp] 
    @ProductName nvarchar(250), 
    @ProductDescription nvarchar(250)
AS
BEGIN
    EXEC sp_addlinkedserver 'linkedserver', 'SQL Server'  

    DECLARE @sql NVARCHAR(MAX)
    DECLARE @err int

    SET @sql = 
        'INSERT INTO [dbo].[Products] ([ProductName], [ProductDescription]) '+
        'VALUES (@ProductName, @ProductName)' 
    EXEC @err = [linkedserver]...sp_executesql 
       @sql, 
       N'@ProductName nvarchar(250), @ProductDescription nvarchar(250)',
       @ProductName, @ProductName;

    RETURN @err
END

As an additional explanation, you do not need to mix EXECUTE and sp_executesql in this situation. Select one of these two approaches.

score 0 · Answer 2 · answered Apr 09 '20 at 06:33

use tempdb  
go
CREATE TABLE [dbo].[Products](
    [ProductName] [varchar](300) NULL,
    [ProductDescription] [varchar](300) NULL
) ON [PRIMARY]



DECLARE @query NVARCHAR(MAX)

    SET @query = 'INSERT INTO [dbo].[Products] ([ProductName], [ProductDescription]) VALUES (@ProductName,@ProductDescription)' 
  EXEC sp_executesql @query, N'@ProductName varchar(250), @ProductDescription varchar(250)', @ProductName='ProductNameVal', @ProductDescription='ProductDescriptionVal';

  select * from Products

+----------------+-----------------------+
|  ProductName   |  ProductDescription   |
+----------------+-----------------------+
| ProductNameVal | ProductDescriptionVal |
+----------------+-----------------------+

If you want to execute it in the remote server you can try it as below; Executing remote stored procedure within sp_executesql

EXEC remoteserver.database.dbo.sp_executesql 'dynamic SQL'

Pass multiple parameters using sp_executesql

2 Answers2