sp_executesql在sql Server 2012中的动态sql查询中未正确设置变量

Question

在下面的查询中，我试图使用sp_executesql在SQL Server 2012中执行的动态查询来设置@productsExist的值。问题是，即使表@tableName存在并且包含记录，在执行动态查询之后，productsExist的值始终是null。

问题：为什么即使表存在并且有记录，查询也会为@productsExist返回null？

DECLARE @productsExist INT;
DECLARE @countQuery NVARCHAR(MAX) = 'IF OBJECT_ID(@tableName, N''U'') IS NOT NULL 
                     begin  select top(1) @productsExist = 1  from ' + @tableName + ' end';
EXECUTE sp_executesql @countQuery, N'@tableName varchar(500),@productsExist INT',
              @tableName = @tableName,
              @productsExist = @productsExist;
select @productsExist as ProductsExist--returns always a NULL value for ProductsExist

Answer 1

您需要将@productsExist参数声明为OUTPUT:

[OUT|OUTPUT]

表示该参数是输出参数

DECLARE @productsExist INT
        ,@tableName SYSNAME = 'tab';
DECLARE @countQuery NVARCHAR(MAX) = 
N'IF OBJECT_ID(@tableName, N''U'') IS NOT NULL 
  begin  select top(1) @productsExist = 1  from ' + QUOTENAME(@tableName) + ' end';
EXECUTE dbo.sp_executesql 
        @countQuery,
        N'@tableName SYSNAME ,@productsExist INT OUTPUT',     -- here
        @tableName = @tableName,
        @productsExist = @productsExist OUTPUT;               -- here
SELECT @productsExist as ProductsExist;

SqlFiddleDemo

---

如果指定的表中没有记录，则@productsExist将返回NULL。如果您想要1表示现有记录，0表示无记录，请使用：

DECLARE @countQuery NVARCHAR(MAX) = 
N'IF OBJECT_ID(@tableName, N''U'') IS NOT NULL 
  BEGIN
    IF EXISTS (SELECT 1 FROM '+ QUOTENAME(@tableName) + ')
       SELECT @productsExist = 1
    ELSE 
       SELECT @productsExist = 0
  END'; 

SqlFiddleDemo2

结果：

table not exists          => NULL
table exists no records   => 0
table exists >= 1 records => 1