Dynamic SQL

Transact-SQL
1

Hi,

Table:

Conta Vdb0 Vdb1 VDb2 .... Vdb12
221 10 20 30 40

I need to update the value of the column VDBx(money) with Dynamic SQL

    DECLARE	@conta Varchar(20) = '62189292'
DECLARE @campo VARCHAR(10) = 'VDB1'
DECLARE @valor MONEY  = 120
    DECLARE @sqlCommand NVARCHAR(1000)

  SET @sqlCommand = 'UPDATE CCTPOC SET @campo = @campo + @valor 
      FROM CCTPOC a INNER JOIN dbo.tvcontas(@conta) b ON a.COD_CONTA=b.cConta 
       where TP_CONTA=''A''
         
  EXECUTE sp_executesql @sqlCommand, N'@conta varchar(20),@campo varchar(10),@valor money', @conta      ,@campo,@valor

The problem is the Column VDBx is money (updated with @campo Var) ...

Msg 235, Level 16, State 0, Line 1
Cannot convert a char value to money. The char value has incorrect syntax.

Regards

2

Either change declare to money or convert on the fly.
Another thing: shouldn't the "SET @campo=..." be "SET campo=..." ?

3

"@campo" is a parameter from a SP(could be VDBx) , and it could be any of columns

VDB1... VDB2... VDB2

The goal is to update the value existing in the column that is referenced in the @campo with the new Value

Example:
Conta Vdb1
'221' 10

update vdb1 set vdb1=vdb1 + 20 where conta='221'

I send the name of column to the SP.

Your Sugestion;

DECLARE @campo VARCHAR(10) = 'VDB1' //MONEY ??

Or on the Fly

EXECUTE sp_executesql @sqlCommand, N'@conta varchar(20),@campo varchar(10),@valor money', @conta ,@campo,@valor //convert(money,@campo) ??

Regards

4

Sorry, I must have misread.
If I understand correctly, you want to concat a varchar and money field.
To do that: @campo+convert(varchar(10),@valor)

5

Hi,

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'

Regards

6

Uupps :smile:

I think i found a solution

DECLARE @campo VARCHAR(10) = 'VDB1'
DECLARE @valor MONEY = 20.01
DECLARE @sqlCommand NVARCHAR(1000)

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

Thanks to all

Regards

PSF