CODEWITHSUNDEEP
sqlstored-procedureshana
Sauerbrei
Sauerbrei

Reputation: 433

Conditional WHERE-clause at stored procedures

I want to apply an additional WHERE-Parameter, if a specific value is given. We're currently working on SAP HANA, but we may are able to adapt strict stored-procedure programming to this platform. any help is very appreciated! So here's the Code:

PROCEDURE test (
   IN id integer,
   IN testVal VARCHAR(20) DEFAULT '*',
   out ex_return DB-SCHEME     
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
--DEFAULT SCHEMA <default_schema_name>
READS SQL DATA AS
BEGIN

ex_return =
    SELECT  
        L."ID",
        LW."ID"
    FROM DB1 L
        INNER JOIN DB2 LW
            ON L."id" = LW."id"
    WHERE 
            L."id" = :id
        AND LW."testVal" LIKE :testVal       -- this one only, if :testVal != '*' 
;

END

What have I tried yet? I tried to CONCAT the SELECT with a calculated WHERE (IF-Conditions) an then the EXECUTE-Command, but it seems like SAP HANA doesn't support that. Then I tried to match the requirements with CASE within the WHERE-Clause:

WHERE ... CASE :wert <> '*' THEN ... END

Upvotes: 0

Views: 1163

Answers (2)

Lars Br.
Lars Br.

Reputation: 10396

Why exactly can't you use the APPLY_FILTER function? 

PROCEDURE test (
   IN id integer,
   IN testVal VARCHAR(20) DEFAULT '',
   out ex_return DB-SCHEME     
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
--DEFAULT SCHEMA <default_schema_name>
READS SQL DATA AS
BEGIN

ex_return =
    SELECT  
        L."ID",
        LW."ID"
    FROM DB1 L
        INNER JOIN DB2 LW
            ON L."id" = LW."id"
    WHERE 
            L."id" = :id;

ex_filtered = APPLY_FILTER (:ex_return, :testVal);

END;

In the variable testVal you now can provide the whole condition, e.g.

call test (1, ' "testVal" like ''%ABC%'' ');

All that is covered in the documentation http://help.sap.com/saphelp_hanaplatform/helpdata/en/a0/9d584807f84477a64d7625ca45b089/content.htm?frameset=/en/a9/4461d71e8145a78d990deac4823858/frameset.htm&current_toc=/en/ed/4f384562ce4861b48e22a8be3171e5/plain.htm&node_id=73

  • Lars

Upvotes: 0

Johan
Johan

Reputation: 929

AND (LW."testVal" LIKE :testVal OR :testVal = '*')

Upvotes: 1

Related Questions