pseudocode pseudocode - 1 year ago 60
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)

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 != ''))

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


SET @j = @j+1

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 Source

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"

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download