Процедура T-SQL, ошибка скалярной переменной даже после успешного обновления

--sp_executesql version
    --SET @SQLQUERY = 'UPDATE @TableName SET Brief = @Brief,
    --               [Full] = @Full,                        
    --               CreatedBy = @CreatedBy,
    --               Department = @Department,
    --               Answer = @Answer WHERE Id=@Id';
 --SET @ParamDefinition=N'@TableName nvarchar(50),@Brief nvarchar(50),@Full nvarchar(MAX),@CreatedBy varchar(256),@Department varchar(256),@Answer nvarchar(MAX),@Id int' 
--  exec sp_executesql @SQLQUERY,@ParamDefinition,@TableName,@Brief,@Full,@CreatedBy,@Department,@Answer,@Id; 

-- exec version
SET @SQLQUERY = 'UPDATE ' + @TableName + ' SET  
                     Brief ='+ @Brief+',
                     [Full] ='+ @Full+',                        
                     CreatedBy ='+ @CreatedBy+',
                     Department ='+ @Department+',
                     Answer ='+@Answer+' WHERE Id='+CAST(@Id as nvarchar(10))

print @SQLQUERY; 
EXEC (@SQLQUERY)

Я использовал обе процедуры EXEC и sp_executesql для выполнения моего динамического запроса, но обе они не работают.

В случае EXEC динамический запрос не задается переменной @SQLQUERY (видно после отладки), в случае sp_executesql я получаю скалярную переменную error, хотя база данных обновляется и я уже все ей передал.

1 9
sql sql-server tsql stored-procedures dynamic-sql

1 ответ:

Случай очень простой. Вы не можете параметризовать имя таблицы / столбца в UPDATE заявление: 

SET @SQLQUERY = 'UPDATE @TableName       --here is problem
                 SET    Brief = @Brief, 
                        [Full] = @Full,                        
                        CreatedBy = @CreatedBy,
                        Department = @Department,
                        Answer = @Answer 
                 WHERE Id=@Id';


SET @ParamDefinition=N'@TableName nvarchar(50),@Brief nvarchar(50), 
                       @Full nvarchar(MAX), @CreatedBy varchar(256),
                       @Department varchar(256),@Answer nvarchar(MAX),@Id int' 

EXEC dbo.sp_executesql @SQLQUERY,@ParamDefinition,
                        @TableName,@Brief,@Full,
                        @CreatedBy,@Department,@Answer,@Id;

Вместо этого используйте замену: 

SET @SQLQUERY = N'UPDATE <tab_name> 
                  SET Brief     = @Brief, 
                     [Full]     = @Full,                        
                     CreatedBy  = @CreatedBy,
                     Department = @Department,
                     Answer     = @Answer 
                  WHERE Id = @Id';

SET @SQLQUERY = REPLACE(@SQLQUERY, '<tab_name>', QUOTENAME(@TableName));

SET @ParamDefinition=N'@Brief nvarchar(50),@Full nvarchar(MAX),
                       @CreatedBy varchar(256),@Department varchar(256),
                       @Answer nvarchar(MAX),@Id int';

EXEC [dbo].[sp_executesql] @SQLQUERY,
                           @ParamDefinition,
                           @Brief,@Full,@CreatedBy, @Department,@Answer,@Id;

Примечания:

  • имя таблицы должны иметь SYSNAME тип данных.
  • рекомендуется цитировать идентификаторы с помощью QUOTENAME (чтобы избежать возможных атак SQL-инъекций).
  • я предполагаю, что @CreatedBy является datetime, поэтому я не понимаю, почему он передается как varchar(256).
  • хорошей практикой является заканчивать каждое утверждение словами: ;. В будущих версиях это будет обязательным.
6