Syntax error on multiple CASE

Question

I have a stored procedure like this:

DECLARE @comando as varchar(5000)

SET @comando = 'DTEXEC  /FILE \"" /de "pass" /CHECKPOINTING OFF  /REPORTING EW'
SELECT @comando = @comando + ' /SET "\"\Package.Variables[' + Replace(str_NombreVariable,'User::','') + '].Value\"";' 
   + CASE WHEN str_NombreVariable LIKE '%v_sCadenaConexion%'
          THEN '"\"'+ str_ValorVariable +'\""' 
              ELSE str_ValorVariable + 
               CASE WHEN str_NombreVariable LIKE '%v_sRutaArchivo%' 
                    THEN @v_sRutaArchivo + '.csv\""' 
                    ELSE '' 
               END 
          END + ''
from [Catalogo].[catVariablesEtl] where IdPaquete = @idPaquete

I want to add another validation with another case, so I try:

SELECT @comando = @comando + ' /SET "\"\Package.Variables[' + Replace(str_NombreVariable,'User::','') + '].Value\"";' 
   + CASE WHEN str_NombreVariable LIKE '%v_sCadenaConexion%'
   + CASE WHEN str_NombreVariable LIKE '%v_sRutaArchivo%'
          THEN '"\"'+ str_ValorVariable +'\""' 
              ELSE str_ValorVariable + 
               CASE WHEN str_NombreVariable LIKE '%v_sRutaArchivo%' 
                    THEN @v_sRutaArchivo + '.csv\""' 
                    ELSE '' 
               END 
          END + ''
from [Catalogo].[catVariablesEtl] where IdPaquete = @idPaquete

I get

incorrect syntax near "from"

What am I doing wrong? I need to add another THEN sentence if I add another CASE? regards

As comment of missing statement I change it to:

UPDATE:

SELECT @comando = @comando + ' /SET "\"\Package.Variables[' + Replace(str_NombreVariable,'User::','') + '].Value\"";' 
   + CASE WHEN str_NombreVariable LIKE '%v_sCadenaConexion%'  
          THEN '"\"'+ str_ValorVariable +'\""' 
          + CASE WHEN str_NombreVariable LIKE '%v_sRutaArchivo%'
          THEN'"\"' +str_ValorVariable +'' 
              ELSE str_ValorVariable + 
               CASE WHEN str_NombreVariable LIKE '%v_sRutaArchivo%' 
                    THEN @v_sRutaArchivo + '.csv\""' 
                    ELSE '' 
               END 
                 END
          END + ''
from [Catalogo].[catVariablesEtl] where IdPaquete = @idPaquete

but when I do a select it return NULL value. Why it happens?

You're probably getting null values because one or more of the columns have a null value. Anything plus `NULL` will result in `NULL`. Perhaps you need to use `ISNULL()` around the columns to see what's causing the problem. — BJones, Feb 16 '18 at 23:17
check this [Best way to do nested case statement](https://stackoverflow.com/questions/505747/best-way-to-do-nested-case-statement-logic-in-sql-server) — Sphinx, Feb 16 '18 at 23:23
I think problem is because `Case` don't have `Else` clause or it doesn't metter?@bjones — , Feb 17 '18 at 00:10

score 0 · Accepted Answer · edited Feb 18 '18 at 01:58

You can use CONCAT() to Concatenate several expressions together:.

I have edited your case logic since the else part's case when logic is not need.

SELECT @comando = CONCAT ( @comando , ' /SET "\"\Package.Variables[' , 
                           Replace(str_NombreVariable,'User::','') , '].Value\"";', 
                           CASE WHEN str_NombreVariable LIKE '%v_sCadenaConexion%'  
                                THEN '"\"'+ str_ValorVariable +'\""' 
                                WHEN str_NombreVariable LIKE '%v_sRutaArchivo%'
                                THEN'"\"' +str_ValorVariable +@v_sRutaArchivo+'.csv""' 
                                ELSE str_ValorVariable                 
                            END 
                        )
FROM 
     [Catalogo].[catVariablesEtl] where IdPaquete = @idPaquete

Do read more about CASE Expression Here

Syntax error on multiple CASE

1 Answers1