Sql-server – Conditional WHERE Clause in a Table Valued Function

sql-server-2012t-sql

I have a table valued function 

    CREATE FUNCTION [dbo].[DateRange] 
    (
        @StartDate date,
        @EndDate date,
        @Location varchar(25),
        @Device varchar(25)
    ) 
    RETURNS TABLE 
    AS
    RETURN 
    (
        SELECT *
        FROM MyTable
        WHERE Date < @EndDate AND
              Date > @StartDate AND
              Location = @Location AND
              Device = @Device 

    );

Now I would like to give the user the option to use NULL as location parameter and or device parameter so he/she gets all data independent of the location and device back

What would be the most elegant and efficient way to do this ?

Best Answer

Like so :

CREATE FUNCTION [dbo].[DateRange] 
    (
        @StartDate date,
        @EndDate date,
        @Location varchar(25) = NULL,
        @Device varchar(25) = NULL
    ) 
    RETURNS TABLE 
    AS
    RETURN 
    (
        SELECT *
        FROM MyTable
        WHERE 
          Date < @EndDate 
          AND Date > @StartDate 
          AND ( @Location IS NULL OR (@Location IS NOT NULL AND Location = @Location))
          AND ( @Device IS NULL OR (@Device IS NOT NULL AND Device = @Device ))

    )

IF the device and location columns can actually be NULL, you might want to consider this option though:

CREATE FUNCTION [dbo].[DateRange] 
    (
        @StartDate date,
        @EndDate date,
        @Location varchar(25) = NULL,
        @Device varchar(25) = NULL
    ) 
    RETURNS TABLE 
    AS
    RETURN 
    (
        SELECT *
        FROM MyTable
        WHERE 
          Date < @EndDate 
          AND Date > @StartDate 
          AND ( @Location IS NULL OR Location = @Location)
          AND ( @Device IS NULL OR Device = @Device )

    )

This would work if you're using a UD table type for the location, as you asked about in your comment. Just make sure the input parameter is Read Only. However, you cannot pass NULL into a table type parameter, you have to pass in an empty parameter object of that type :

CREATE TYPE LocationsType AS TABLE 
( LocationName VARCHAR(25));

CREATE TABLE MyTable (Date datetime, Location varchar(25), Device varchar(25));

CREATE FUNCTION [dbo].[DateRange] 
    (
        @StartDate date,
        @EndDate date,
        @Location LocationsType READONLY,
        @Device varchar(25) = NULL
    ) 
    RETURNS TABLE 
    AS
    RETURN 
    (
        SELECT *
        FROM MyTable
        WHERE 
          Date < @EndDate 
          AND Date > @StartDate 
          AND ( ((select count(*) FROM @Location) = 0) OR Location IN (select LocationName from @Location))
          AND ( @Device IS NULL OR Device = @Device )

    )

Call it:

declare @l LocationsType

select *
from [dbo].[DateRange] (getDate(), GetDate(), @l,NULL)
Related Question