Dynamic sql query with stored procedure and table variable

Question

I want to call a stored procedure dynamically as my names of the procedures are stored somewhere, also I need to store the result of that procedure into a table variable. Hence I had to write following sql code,

In following code @tblEmailIds is the table variable which I want to store the result of SP in @tempEmailSource is the name of the procedure @tempRecipientsIdsCSV is the first argument that my SP is accepting @ObjectMasterId is the second argument that SP is accepting (optional)

DECLARE @tempTypeName NVARCHAR(100), 
            @tempTypeId INT,
            @tempEmailSource NVARCHAR(100),
            @tempRecipientsIdsCSV NVARCHAR(MAX),
            @tempIsObjectSpecific BIT,
            @sqlQuery NVARCHAR(MAX) = 'INSERT INTO @tblEmailIds '
SELECT TOP 1 @tempTypeName = NAME, 
@tempTypeId = Id,         
@tempEmailSource = EmailListSourceName 
FROM @tbleRecipientsTypes WHERE IsEmailIdsFetched = 0

    SELECT @tempRecipientsIdsCSV = SUBSTRING(
    (SELECT ',' + CAST(RT.EmailRecipientId AS NVARCHAR(50))
    FROM @tbleRecipientsTypes RT WHERE RT.Id = @tempTypeId
    ORDER BY RT.EmailRecipientId
    FOR XML PATH('')),2,200000)

    SELECT @tempRecipientsIdsCSV


    SET @sqlQuery = @sqlQuery + 'EXEC ' + @tempEmailSource +' ' +''''  +    @tempRecipientsIdsCSV +''''
    IF (@tempIsObjectSpecific = 1)
    BEGIN
        SET @sqlQuery = @sqlQuery + ' ' + @ObjectMasterId
    END

    PRINT @SQLQUERY

    EXECUTE SP_EXECUTESQL 
    @SqlQuery,'@IdsCSV NVARCHAR(MAX) OUTPUT,  
    @ObjectMasterId INT = NULL OUTPUT', @tblEmailIds

I am getting the following error

Msg 214, Level 16, State 3, Procedure sp_executesql, Line 6 Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'.

Answer 1

Prefix N to the @params of SP_EXECUTESQL.

Also you need to store the result of OUTPUT parameter

Declare  @IdsCSV NVARCHAR(MAX),@ObjectMasterId INT = NULL

EXECUTE SP_EXECUTESQL 
    @SqlQuery,
    N'@IdsCSV NVARCHAR(MAX) OUTPUT,@ObjectMasterId INT = NULL OUTPUT',
    @IdsCSV = @IdsCSV OUTPUT,
    @ObjectMasterId = @ObjectMasterId OUTPUT

Answer 2

There are quite a few problems here.

1. As the error message clearly states, the parameter list needs to be NVARCHAR so just prefix that string literal with an N (as also stated in @VR46's answer).

2. Table variables do not work the way that you are trying to use them. First, you do not ever declare @tblEmailIds, but even if you did, the scope of a table variable is local, and they cannot be used as OUTPUT parameters. Instead, you need to create a local temporary table (i.e. #tblEmailIds) and do INSERT INTO #tblEmailIds.

3. You reference another table variable, @tbleRecipientsTypes, that has not been declare or populated.

4. You do declare @tempIsObjectSpecific but never set it so it will always be NULL.

5. Why is @IdsCSV (in the sp_executesql call parameter list) declared as OUTPUT? Not only are you not passing in @tempRecipientsIdsCSV (to be @IdsCSV in the dynamic SQL), it isn't even necessary to be a parameter since you are directly concatenating the value of @tempRecipientsIdsCSV into the Dynamic SQL, and there is no @tempRecipientsIdsCSV variable in the Dynamic SQL to begin with. So remove @IdsCSV NVARCHAR(MAX) OUTPUT, from the parameter list.

6. You say that "@tempRecipientsIdsCSV is the first argument that my SP is accepting", but then you declare it in the code, which should result in an error.

7. What datatype is @ObjectMasterId? You say that it is passed into the proc and I see that it is concatenated into the Dynamic SQL, so it needs to either be a string type (i.e. not INT like is shown in the sp_executesql parameter list) or it needs to be in a CONVERT(NVARCHAR(10), @ObjectMasterId.

8. If @ObjectMasterId is being passed in, then why is it declared as OUTPUT in the sp_executesql parameter list? But the better question is probably: why are you even passing it into sp_executesql anyway since you directly concatenate it into the Dynamic SQL? There is no @ObjectMasterId variable being used in the Dynamic SQL.