pseudocode pseudocode - 1 month ago 10
SQL Question

How to update table with while loop in Stored Procedure?

I want to update my dynamic table in while loop.

Here is my code:

While (@j<=@tmp_getRowCount)
Begin

Set @firstcolumn = (Select SplitFirst_tblAR from @result_AR Where rownumber = @j) //String//
Set @secondcolumn = (Select EMail_tblAR from @result_AR Where rownumber = @j) //String//
Set @thirdcolumn = (Select SplitFirst_tblKul from @result_AR Where rownumber = @j) //String//
Set @fourthcolumn = (Select EMail_tblKul from @result_AR Where rownumber = @j) //String//

insert into @test Values(@tmp_ID, @firstcolumn,@secondcolumn,@thirdcolumn,@fourthcolumn)

if ((@firstcolumn = @thirdcolumn) AND (@secondcolumn != @fourthcolumn) AND (@firstcolumn != ''))
begin

Set @q_updateTable = 'Update '+ quotename(@tablename) +' Set '+@columnname+' = ''' + @fourthcolumn + ''' Where ID = ' + @tmp_ID + ''
Exec sp_executesql @q_updateTable

end

SET @j = @j+1
End


My result_AR table:

enter image description here

I know the error is in here:


Where ID = ' + @tmp_ID + ''


When I change this Where clause as,


Where '+@columnname+' = ''' + @secondcolumn + ''' '


code works correctly.

Why can't I set as ID my where clause? I am getting ID value as integer.
The error is 'Query completed with errors'.
Thanks in advance.

Answer

you can not set Id in where clause because the id is integer value and you are concatenating it with string (varchar). So first you have to convert it in (String)varchar and the you can use it where clause.

Like :

Set @q_updateTable = 'Update '+ quotename(@tablename) +' Set '+@columnname+' = ''' + @fourthcolumn + ''' Where ID = ' + convert(varchar,@tmp_ID) + '' Exec sp_executesql @q_updateTable

you have to use "convert(varchar,@tmp_ID)" insted of "@tmp_ID"