1

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?

Mureinik
  • 297,002
  • 52
  • 306
  • 350
Nishant Roy
  • 1,043
  • 4
  • 16
  • 35
  • 1
    This would normally be done using dynamic SQL (not too recommended for time-critical queries and/or highly frequent execution). You build your query as a string (including the conditions if the parameters are passed) and then execute it (check the exact syntax my MS-SQL). – FDavidov Aug 01 '16 at 05:50

3 Answers3

2

Basically, the requirement is to not apply any condition is deviceType IS NULL. Instead of altering the query dynamically, you could just construct a condition that always returns true in such a situation by using the logical or operator:

SELECT TOP 5 DISTINCT USERS 
FROM   MYTABLE
WHERE  deviceType IS NULL OR USERDEVICE = deviceType;
Mureinik
  • 297,002
  • 52
  • 306
  • 350
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);
Lars Br.
  • 9,949
  • 2
  • 15
  • 29
-2
DECLARE @deviceType VARCHAR(100)
DECLARE @SQL VARCHAR(256)
    ,@sql1 VARCHAR(256) = 'WHERE USERDEVICE = ''' + @deviceType + ''''

SET @SQL = 'SELECT TOP 5 DISTINCT USERS FROM MYTABLE'
SET @SQL = CASE 
        WHEN @deviceType IS NULL
            THEN @SQL
        ELSE @SQL + ' ' + @sql1
        END

EXEC (@SQL)
brahmareddy
  • 121
  • 8