CODEWITHSUNDEEP
sql-server
Clark
Clark

Reputation: 23

How to use Table Variable in sp_executesql with JSON Data

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`

Upvotes: 0

Views: 137

Answers (1)

Venkataraman R
Venkataraman R

Reputation: 12969

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

Upvotes: 2

Related Questions