如何在T-SQL中动态地使用表类型?

I want to create a stored procedure with dynamic parameters. One of these parameters is a table type

我想创建一个包含动态参数的存储过程。其中一个参数是表类型

CREATE TYPE [dbo].[IdTable] AS TABLE ([Id] [int] NULL)
GO

CREATE PROCEDURE [dbo].[SP_deleteCells]
    @table IdTable READONLY,
    @tableName NVARCHAR(50),
    @fieldName NVARCHAR(50),
    @result BIT OUTPUT
AS
    DECLARE @SQL NVARCHAR(500);

    SET @SQL='delete from TBL_CustomerTerminal where ID in (select ID from @table)'
    EXEC (@SQL);

    SET @result = @@ROWCOUNT;

How can I exec this code without errors?? Right now, I get:

我如何能在没有错误的情况下执行此代码?现在,我得到:

Must declare the table variable "@table"

必须声明表变量“@table”

2 个解决方案

#1

Use sp_executesql

使用sp_executesql

exec sp_executesql N'delete from TBL_CustomerTerminal where ID in (select ID from @table)'
  , N'@table dbo.IdTable readonly'  /* parameter declaration for sp_executesql */
  , @table /* pass the parameters */

#2

It doesn't seem that you need dynamic SQL for the query above. But I assume it is just a sample.

上面的查询似乎不需要动态SQL。但我假设它只是一个样本。

The dynamic SQL query has it's own code visibility. It can't see any variable outside of the locally defined variables. If you want to pass parameters to your query you need to use sp_executesql instead of EXEC.

动态SQL查询具有自己的代码可视性。它在局部定义的变量之外看不到任何变量。如果要向查询传递参数，需要使用sp_executesql而不是EXEC。

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql

#1