Блог

Привет, я пытаюсь использовать 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`
  

Вы должны определить определенный пользователем тип таблицы, а затем передать его в 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