my excuses ... i will clarify with a simple example
I want to replace this code:(i have several colums Vdb1,vdb2..Vdb12... the SP sends the name of the column to update(ex VDB2) , but its content is money)
Actualy i do ...
if @campo='VDB1' THEN
update vdb1 set vdb1=vdb1 + 20 where conta='221'
else if @campo='VDB2' THEN
update vdb2 set vdb2=vdb2 + 20 where conta='221'
...
else if @campo='VDB12' THEN
update vdb2 set vdb12=vdb12 + 20 where conta='221'
I want to update with Dynamic SQL (not to test with if else)..
SET @sqlCommand = 'UPDATE CCTPOC SET @campo = @campo + @valor
where conta='221'
SET @sqlCommand = 'UPDATE #tt SET '+@campo +'+=' + CONVERT(VARCHAR(20),@valor) +
' where id=1'
EXECUTE sp_executesql @sqlCommand
PS: No parameters needed on sp_executeSQL
Although i don't need to test If .. ELSE ... and only one line to UPDATE , i've my doubts about what SQL makes more Fast (if ELSE on several conditions ) or SQL DYNAMIC