sql server - Select varchar value as column name inside dynamic sql query -

sql server - Select varchar value as column name inside dynamic sql query -

in project using sql server 2008 end. facing issue in procedure using dynamic sql. see sample code dynamic sql using.

declare @sql varchar(max) set @sql='declare @counter1 int=1; declare @counter2 int; declare @prevmonthamt decimal(18,2); declare @currmonthamt decimal(18,2); declare @monthname varchar(100); while @counter1<=(select count(*) #tab1) begin set @counter2 = (select count(*) #tabmonths) set @monthname = (select [month] #tabmonths id=@start) while @counter2>=1 begin select @currmonthamt=@monthname #tab1 id=@counter1 select @prevmonthamt=@monthname #tab1 id=(@counter1-1) update #tab1 set @monthname=(@currmonthamt-@prevmonthamt) id=@counter1 set @counter2=@counter2-1 end set @counter1= @counter1+1 end; select *from #tab1 ' exec @sql

here temporary table #tab1 contains employee month wise salary details , #tabmonths contains list of months. here month columns in both #tab1 & #tabmonths not static fields. automatically generate user drop downwards selection (start year , end year) front end end ui. if user selections start year:2013 & end year:2014, create 24 dynamic month columns in both #tab1 & #tabmonth jan, 2013, feb, 2013 dec, 2014.

i want update difference between current month salary , previous month salary in #tab1. @monthname doesn't consider column name treats @monthname varchar while selecting @tab1. don't salary of employee particular month. must want possible within dynamic query. help appreciated.

your problem in these 3 lines of code. first , sec lines attempting assign varchar decimal variable. 3rd line attempting assign decimal value varchar variable.

select @currmonthamt=@monthname #tab1 id=@counter1 select @prevmonthamt=@monthname #tab1 id=(@counter1-1) update #tab1 set @monthname=(@currmonthamt-@prevmonthamt) id=@counter1

as mental exercise, let's replace variables values here. assume #tab1 contains info below, ... represents months 4-10, april-october.

id monthname --------------- 1 jan 2 feb 3 march ... ... 11 nov 12 dec

focus on line of code:

select @currmonthamt=@monthname #tab1 id=@counter1

substitute 2 @counter1, , "february" @monthname, code looks this:

select @currmonthamt="february" #tab1 id=2

you trying assign @currmonthamt (a decimal representing someone's salary) varchar value "february" instead of decimal value, 5000.00. personally, i'd rather paid 5000.00 instead of "february".

sql sql-server sql-server-2008