SQL: SAP Hana if parameter is null, ignore where

Question

I'm passing 3 parameters into my Hana Stored Procedure to use as WHERE clauses, and if the parameter is null, I want the procedure to behave as though that condition doesn't exist.

example:

if one of the input parameters is deviceType.

SELECT TOP 5 DISTINCT USERS FROM MYTABLE
WHERE USERDEVICE = deviceType;

if deviceType is null, query should simply be

SELECT TOP 5 DISTINCT USERS FROM MYTABLE.

I know I can achieve this with if statements, but is there another way to do it?

Answer 1

When using SQLScript you can use the APPLY_FILTER() function.

E.g.

drop procedure getTopUsers;
create procedure getTopUsers (IN filter_cond NVARCHAR(200)) as
begin

  vUsers = SELECT DISTINCT user_name, creator FROM USERS;

  if (:filter_cond is NULL)  then
     TopUsers = select TOP 5 user_name FROM :vUsers;
  else
     tTopUsers = APPLY_FILTER(:vUsers, :filter_cond);
     TopUsers = SELECT TOP 5 user_name FROM :tTopUsers;
  end if;

  SELECT user_name FROM :TopUsers;
end;



call getTopUsers ('CREATOR != ''SYS'' ');
call getTopUsers (NULL);