sql - Get the column name as a parameter from SP and set a value to that Column with in the procedure -

alter proc [dbo].[sp_dosomethingwithstudentattn] @attnlist dbo.studentattendancelist readonly,@deptid int,@semid int,@secid int,@dayid int,@currentdate date,@period nvarchar(5) declare @ifstudentdaywiseattnexists bit,         @updatequery varchar(max),         @insertquery varchar(max)  begin  set nocount on declare      @stnid int,     @stnattnstatus nvarchar declare cur_updateinsertstnattndata cursor  forward_only static       select studentid,attdnstatus  @attnlist open cur_updateinsertstnattndata if @@cursor_rows>0 begin     fetch next cur_updateinsertstnattndata @stnid,@stnattnstatus     while @@fetch_status=0     begin          set @ifstudentdaywiseattnexists=(select case when exists                                              (                                                 select * tbl_dailyattend                                                  deptid=@deptid ,                                                  semid=@semid ,                                                  secid=@secid ,                                                  attdate=@currentdate ,                                                  stuid=@stnid                                             )                                             cast(1 bit)                                             else cast(0 bit)                                             end                                          )           if @ifstudentdaywiseattnexists='true'          begin             if @stnattnstatus='true'             begin                 select @updatequery='update tbl_dailyattend set '+@period+'=''true'' deptid='+@deptid+' , semid='+@semid+' , secid='+@secid+' , attdate='+convert(date, @currentdate)+' , stuid='+@stnid+''                 exec  (@updatequery)             end             else             begin                 select @updatequery='update tbl_dailyattend set '+@period+'=''false'' deptid='+@deptid+' , semid='+@semid+' , secid='+@secid+' , attdate='+convert(date, @currentdate)+' , stuid='+@stnid+''                 exec  (@updatequery)             end           end          else          begin                                            set @insertquery='insert tbl_dailyattend (deptid,semid,secid,dayid,attdate,'+@period+') values ('+@deptid+','+@semid+','+@secid+','+@dayid+','+@currentdate+','+@stnattnstatus+')'          end          fetch next cur_updateinsertstnattndata @stnid,@stnattnstatus     end end end 

since building dynamic sql, need build statements strings - i.e. can't concatenate date query (... ='+convert(date, @currentdate)+' ...).

you should try , parameterize dynamic query far possible reduce sql injection surface attack area. example, in update query, can parameterized exception of dynamic column name (@period). use sp_executesql pass parameters, , corresponding types across dynamic query:

declare @updatequery nvarchar(max);  select @updatequery=      n'update tbl_dailyattend set ' + @period + n'=''true''         deptid=@deptid , semid=@semid , secid=@secid               , attdate = @currentdate , stuid=@stnid';  sp_executesql @updatequery,       n'@deptid int,         @semid int,         @secid int,         @dayid int,         @currentdate date,        @stnid int',       @deptid = @deptid,        @semid = @semid,        @secid = @secid,        @dayid = @dayid,        @currentdate = @currentdate,       @stnid = @stnid'; 

to reduce injection attack on dynamic column name, can check column exists in table prior executing query (e.g. sys.columns)