CODEWITHSUNDEEP
sqlsql-serversql-server-2008case
Omar Krichen
Omar Krichen

Reputation: 163

How create SQL command with CASE expression in one of parameters?

I want to create a SQL query in a SqlCommand located in OLE DB command to update an existing database from file txt and one information depend of the other. For example if I have decision=non I'll have somme=0 else somme=(I add five values)

It worked when I add data manually like somme=0 or somme=val_1+val_2+val_3+val_4+val_5 without a case expression.

UPDATE [TP1].[dbo].[Data] 
SET nom=?, val_1=?, val_2 =?, val_3=?, val_4=?, val_5=?, decision=?,
    somme= (CASE 
               WHEN (STRCMP(decision,"NON") < 1)
                  THEN 0
                  ELSE (val_1 + val_2 + val_3 + val_4 + val_5)
            END)
WHERE (numero = ?)

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You see to want to name your parameters, which you can do in a values() clause:

update d
    SET nom = v.nom,
        val_1 = v.val_1,
        val_2 = v.val_2,
        val_3 = v.val_3,
        val_4 = v.val_4,
        val_5 = v.val_5,
        decision = v.decision,
        somme = (case when d.decision like '%NON%'
                      then v.val_1 + v.val_2 + v.val_3 + v.val_4 + v.val_5
                      else 0
                 end)
     from [TP1].[dbo].[Data] d cross join
          (values (?, ?, ?, ?, ?, ?, ?)
          ) v(val_1, val_2, val_3, val_4, val_5, decision, numero)
     where d.numero = v.numero;

You could also pass them in as named parameters.

You might find it easier to have somme be a computed column. Then it is always up-to-date. You would remove the current definition and then add the computed column as:

alter table [TP1].[dbo].[Data] add somme as
    (case when d.decision like '%NON%'
          then val_1 + val_2 + val_3 + val_4 + val_5
          else 0
     end);

Upvotes: 1

Related Questions