How to use Table Variable in sp_executesql with JSON Data

Question

Hello I'm trying to use sp_executesql

DECLARE @filterBusiness         TABLE
            (
                [BusinessID]        INT         NULL
            );

INSERT INTO @filterBusiness
            (
                [BusinessID]
            )
    SELECT
                [BusinessID]
    FROM OPENJSON(@jsonData)
    WITH
    (
                [BusinessID]    INT '$.BusinessID'
    )AS [bus]

DECLARE @sqlQuery       NVARCHAR(max) = ''
SELECT @sqlQuery =' SELECT * FROM [Training].[Training]
WHERE 1=1'

   IF EXISTS (SELECT * FROM @filterBusiness)
    BEGIN
        SET @whereClause    =   CONCAT(@whereClause, 'AND [BusinessID] IN (SELECT [BusinessID] FROM @filterBusiness)');
    END

EXEC sp_executesql @sqlQuery,
    N'@filterBusiness TABLE READONLY', @filterBusiness`

Venkataraman R · Accepted Answer · 2020-11-11 03:53:12Z

2

You have to define user defined table type and then pass it to sp_executesql

CREATE TYPE BUSINESSTYPE AS  TABLE
            (
                [BusinessID]        INT         NULL
            );

DECLARE @whereClause VARCHAR(5000) = ''
DECLARE @filterBusiness  BUSINESSTYPE     

INSERT INTO @filterBusiness
            (
                [BusinessID]
            )
    SELECT
              1
select * FROM @filterBusiness

DECLARE @sqlQuery       NVARCHAR(max) = ''
SELECT @sqlQuery =' SELECT * FROM [Training].[Training]
WHERE 1=1'

   IF EXISTS (SELECT * FROM @filterBusiness)
    BEGIN
        SET @whereClause    =   CONCAT(@whereClause, 'AND [BusinessID] IN (SELECT [BusinessID] FROM @filterBusiness)');
    END

EXEC sp_executesql @sqlQuery,
    N'@filterBusiness BUSINESSTYPE READONLY', @filterBusiness

answered Nov 11, 2020 at 3:53

Venkataraman R

13.2k2 gold badges38 silver badges68 bronze badges

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

Collectives™ on Stack Overflow

How to use Table Variable in sp_executesql with JSON Data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related