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'.

Solomon Rutzky · Accepted Answer · 2015-12-26 03:55:02Z

1

There are quite a few problems here.

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).
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.
You reference another table variable, @tbleRecipientsTypes, that has not been declare or populated.
You do declare @tempIsObjectSpecific but never set it so it will always be NULL.
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.
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.
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.
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.

edited Dec 26, 2015 at 3:55

answered Dec 25, 2015 at 21:10

Solomon Rutzky

49.3k11 gold badges141 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Roshan Birwatkar Over a year ago

Thanks, to summarize these are the changes I made, put N'', removed OUTPUT, and made @tblEmailIds as #tblEmailIds ... It worked

Solomon Rutzky Over a year ago

@RoshanBirwatkar You're welcome. I'm glad it worked. However, as I said, you can get rid of the parameter list and parameters entirely since you are not even using them. You can get away with just the EXECUTE SP_EXECUTESQL @SqlQuery; and it should be fine.

Pரதீப் · Accepted Answer · 2015-12-25 16:14:22Z

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

edited Dec 25, 2015 at 16:14

answered Dec 25, 2015 at 16:08

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

1 Comment

Solomon Rutzky Over a year ago

You are correct that the param list needs to be prefixed with an N, but storing the result of the OUTPUT parameter isn't going to help because not only does it seem to not be an OUTPUT param, it isn't a parameter in the first place since it isn't used. In fact, either of those parameters are being used.

Collectives™ on Stack Overflow

Dynamic sql query with stored procedure and table variable

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related