sp_executesql неправильно устанавливает переменную в динамическом SQL-запросе в SQL Server 2012

В приведенном ниже запросе я пытаюсь установить значение @productsExist, используя динамический запрос, который выполняется sp_executesql в SQL Server 2012. Проблема в том, что, хотя таблица @tableName существует и содержит записи, значение productsExist всегда равно null после выполняется динамический запрос.

Вопрос. Почему запрос возвращает значение null для @productsExist, даже если таблица существует и содержит записи?

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

dynamic-sql sql-server-2012 sp-executesql
person Sunil    schedule 26.12.2015    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Вам необходимо объявить параметр @productsExist как 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
person Lukasz Szozda    schedule 26.12.2015
comment
Отличный ответ. Спасибо. Является ли использование SYSNAME вместо varchar для @tableName лучшим вариантом? - person Sunil; 26.12.2015
comment
@Sunil SYSNAME — это просто псевдоним для NVARCHAR(128). Я использую его, потому что это хорошая практика, но varchar также будет работать (до 128 символов). - person Lukasz Szozda; 26.12.2015
comment
Я нашел QUOTENAME что-то новое, но очень полезное, так как оно заключает в квадратные скобки имя таблицы. Я никогда не сталкивался с этим до вашего ответа. Спасибо. - person Sunil; 26.12.2015