Hi, In a stored procedure, I build a temporary table and then I fill it in a loop.
The loop uses dynamic sql to fill the table like this: while begin set @dynSql = "UPDATE #tmptable SET [' + @column_name '] = ''' + @value + ''' WHERE c = ''' + @variable exec @dynSql end The @column_name, @value and @variable variables vary according to the loop condition. The temporary table is not very big, it has 50 columns and 400 rows. The problem is the update runs 13000 times and it takes 40 seconds.
If I remove the dynamic sql and put a hardcoded query that always does the same update, the update time is 5 seconds.
I guess its because the statement is recompiled in each loop iteration when using dynamic sql.
Is there something I can do?