Invalid syntax near dynamic month month

Question

When running this query

update p_leave_allocation_14122015 
set 'month' + @actualMonth  = @actualleavedays
where year = @actualYear  
  and emp_card_no = @emp_card_no 

I get an error message:

Incorrect syntax for about a month.

The table p_leave_allocation_14122015has columns of type month1, month2, month3, .....

In my update request, I want it to monthbe hard-coded and the month of the month will be dynamic based on some conditions that I wrote.

But when I tried with the above request, I got the indicated error.

I am using SQL Server 2005.

Request to offer

Answer 1

You should try something like below,

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'update p_leave_allocation_14122015 
           SET MONTH'+CAST(@actualMonth AS VARCHAR(2)) +' = ' + CAST(@actualleavedays AS VARCHAR(10))+
           ' WHERE YEAR = '+ CAST(@actualYear AS VARCHAR(4))+
           ' AND emp_card_no = '+CAST(@emp_card_no AS VARCHAR(100)) +'';

PRINT @sql --Note: Check the query first before updating.
EXEC SP_EXECUTESQL @sql;

Answer 2

For this you need to use dynamic query

DECLARE @sql NVARCHAR(max) = '' 

SET @sql = 'update p_leave_allocation_14122015       set month' 
           + Cast(@actualMonth AS VARCHAR(10)) + ' =  ' 
           + Cast(@actualleavedays AS VARCHAR(10)) 
           + ' where year = @actualYear  and emp_card_no =  @emp_card_no ' 

EXEC Sp_executesql 
  @sql, 
  N'@actualYear int,@emp_card_no int', 
  @actualYear, 
  @emp_card_no 

. @actualMonth @actualleavedays integer, varchar

Answer 3

declare @sql varchar(max)=''
declare @actualMonth int=10
declare @actualleavedays int=20
declare @actualYear int=2015
declare @emp_card_no varchar(8)='Emp1010'

set @sql = 'update p_leave_allocation_14122015 
             set month' + cast(@actualMonth as varchar(2))  + '='+ cast(@actualleavedays as varchar(3))+
           ' where year ='+ cast(@actualYear as varchar(4))  + 'and emp_card_no ='+  @emp_card_no

print @sql --check generated sql query  
exec(@sql)

Answer 4

try it

enter a string and save it in a variable nvarcharand then run the query as shown below

DECLARE @SqlQuery NVARCHAR(MAX)
SET @SqlQuery ='update p_leave_allocation_14122015 set month' + @actualMonth + '=' + @actualleavedays + ' where year = '+  @actualYear + ' and emp_card_no ='+ @emp_card_no 
EXEC(@SqlQuery)