sql-server - 如何在 sp_executesql 中将表变量与 JSON 数据一起使用

Question

您好我正在尝试使用 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`

标签： sql-server

Answer 1

您必须定义用户定义的表类型，然后将其传递给 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