Home / Questions / 50965
Accepted
Alejandro Ricotti
Asked: 2020-02-23 06:27:34 +0800 CST 2020-02-23 06:27:34 +0800 CST

Pass CASE as parameter

  • 772

I have a function that receives parameters and returns a result, the following way works fine:

DECLARE @var varchar (10)
EXEC @var = GetLinea '','','', '' , ''
SELECT @var

But when I want to fill the values ​​that I send by parameter with an CASEerror:

DECLARE @var varchar (10)
EXEC @var = GetLinea '', CASE WHEN '11' = '11' THEN 'SI' ELSE 'SI NO' END ,'', '' , ''
SELECT @var

The error that appears is:

Incorrect syntax near the keyword 'CASE'.

As if I was misusing the CASE.

The code that I pass is in test mode, I must execute the function in a view.

This is the true case, the others are tests I used

SELECT 
IdOferta, 
Location,
PRO, 
Toneladas, 
Espesor, 
Ancho, 


CASE WHEN Location<>''
        THEN 'MP_' + location
     WHEN CHARINDEX('PREPI', clase_desc)>0 Or CHARINDEX('CINCA', clase_desc)>0
        THEN 'REV_CA'
     ELSE 
        GetLinea
             parametro1,
             CASE WHEN Null = parametro2 THEN '' ELSE parametro2 END,
             CASE WHEN Null = parametro3 THEN '' ELSE parametro3 END,
             CASE WHEN Null = parametro4 THEN '' ELSE parametro4 END,
             parametro5
     END
AS Linea_Final
sql

2 Answers

  1. Best Answer

    What you can do is use an intermediate variable as a parameter, since exec does not accept expressions as a parameter:

    DECLARE @var varchar (10),
     @parametro varchar(10) =  (case when '11' = '11'  THEN 'SI' ELSE 'SI NO' END)
EXEC @var = GetLinea_Nodo_Material '', @parametro ,'', '' , ''
SELECT @var

    In the case of a view you can use:

     if '11' = '11'
    begin
        EXEC GetLinea_Nodo_Material '', 'SI' ,'', '' , ''
    end
    else
    begin
        EXEC GetLinea_Nodo_Material '', 'SI NO' ,'', '' , ''
    end

    Note According to your condition of '11' = '11' it will always enter the case of if

    In stored procedure mode:

    CREATE PROCEDURE Procedimiento
(
@VARIABLE VARCHAR(10) OUTPUT,
@VAL1 INT,  
@VAL2 INT,
@VAL3 INT,
@VAL4 INT,
@VAL5 INT
)
AS
BEGIN

DECLARE @PARAMETRO1 VARCHAR(10),
        @PARAMETRO2 VARCHAR(10),
        @PARAMETRO3 VARCHAR(10),
        @PARAMETRO4 VARCHAR(10),
        @PARAMETRO5 VARCHAR(10)


 SET @PARAMETRO1 = case when @VAL1 = '11'  THEN 'SI' ELSE 'SI NO' END
 SET @PARAMETRO2 = (case when @VAL2 = '12'  THEN 'SI' ELSE 'SI NO' END)
 SET @PARAMETRO3 = (case when @VAL3 = '13'  THEN 'SI' ELSE 'SI NO' END)
 SET @PARAMETRO4 = (case when @VAL4 = '14'  THEN 'SI' ELSE 'SI NO' END)
 SET @PARAMETRO5 = (case when @VAL5 = '15'  THEN 'SI' ELSE 'SI NO' END)

EXEC @VARIABLE = GetLinea_Nodo_Material @PARAMETRO1, @PARAMETRO2 ,@PARAMETRO3, @PARAMETRO4 , @PARAMETRO5
SELECT @VARIABLE

SELECT 
IdOferta, 
Location,
PRO, 
Toneladas, 
Espesor, 
Ancho, 


CASE WHEN Location<>''
        THEN 'MP_' + location
     WHEN CHARINDEX('PREPI', clase_desc)>0 Or CHARINDEX('CINCA', clase_desc)>0
        THEN 'REV_CA'
     ELSE 
        @VARIABLE
     END
AS Linea_Final
END
--FROM TU TABLA?

    Note Here it depends on what you really need to handle as parameters you can adjust, add or remove as you need Note2 I don't know if the select comes from a table or from variables from another site but it would be something like that in that form

    Cheers

    • 1

  2. I think you are trying to validate the input parameters of your function GetLinea before passing them to the function.

    In any case, you can do that validation inside the function GetLinea:

    create function GetLinea (
    @par1 varchar(30) = null,
    @par2 varchar(30) = null,
    @par3 varchar(30) = null,
    @par4 varchar(30) = null,
    @par5 varchar(30) = null
)
returns varchar(100)
begin
    declare @resultado varchar(100);

    ...

    set @par1 = isnull(@par1, '');
    set @par2 = isnull(@par2, '');
    set @par3 = case when @par3 is null then '' else @par3 end;
    set @par4 = case when isnull(@par4, '') = 'X' then 'SI' else 'NO' end;
    set @par5 = case when isnull(@par5, 'X') = 'X' then 'Y' else 'Z' end;

    ...

    return @resultado;
end;

    This way from the view you just use the function directly no matter what values ​​you pass to it:

    SELECT 
    IdOferta, 
    Location,
    PRO, 
    Toneladas, 
    Espesor, 
    Ancho, 
    CASE
    WHEN Location<>'' THEN
        'MP_' + location
    WHEN CHARINDEX('PREPI', clase_desc)>0 Or CHARINDEX('CINCA', clase_desc)>0 THEN
        'REV_CA'
    ELSE 
        GetLinea (
            parametro1,
            parametro2,
            parametro3,
            parametro4,
            parametro5
        )
    END AS Linea_Final
    • 1