Aqib Aqib - 7 months ago 9
SQL Question

T-SQL Procedure, scalar variable error even after successful updation

--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)


I have used both
EXEC
and
sp_executesql
procedures to execute my dynamic query but both are failing.

In case of
EXEC
the dynamic query is not set to the
@SQLQUERY
variable (seen after debugging), in case of
sp_executesql
I get scalar variable error though database is updated and I have already passed everything to it.

Answer

Case is very simple. You cannot parametrize table/column name in UPDATE statement:

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;

Use substitution instead:

SET @SQLQUERY = '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;

Notes:

  • Table name should have SYSNAME datatype.
  • It is a good practice to quote identifiers with QUOTENAME (to avoid potential SQL Injection attacks).
  • I guess @CreatedBy is datetime that is why I do not understand why it is passed as varchar(256).
  • It is a good practice to end every statement with ;. In future versions this will be mandatory.