Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

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)
share|improve this answer
    
Is this also possible with a parameter @Location which would be a table valued type ? so I can pass more than one location value ? So if there are values in the table valued type it gets the rows with this specific locations, if there is NULL it returns all rows? –  nojetlag Apr 24 '13 at 6:37
    
that would be probably just slightly more complex, but no big deal. If you want, I'll add that variation –  druzin Apr 24 '13 at 13:39
    
Just added a little to the answer, as per your question in the comments –  druzin Apr 24 '13 at 14:03
add comment

The most efficient way according to my own testing is:
*Remark: only valid for NON-NULLABLE columns, as commented by Aaron.

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 = CASE WHEN @Location IS NULL THEN Location ELSE @Location END
      AND Device = CASE WHEN @Device IS NULL THEN Device ELSE @Device END

);

There is another way, using ISNULL(@Location,Location) instead of CASE expression, however, as elegant as it may be, it is less efficient then CASE.

share|improve this answer
    
This can change the query semantics in the event that the Location or Device column is actually NULL. –  Aaron Bertrand Apr 23 '13 at 13:52
    
@AaronBertrand You are correct, my testings were on NON-NULLABLE columns, added remark to my answer. –  Justicator Apr 23 '13 at 14:01
    
Also can you add some evidence that ISNULL is less efficient than a CASE expression (it's not a statement)? –  Aaron Bertrand Apr 23 '13 at 14:02
    
@AaronBertrand I replaced "statement" with "expression". as for the efficiency of CASE over ISNULL and OR, I will look for a link to post as it has been discussed many times before. Thank you for the remarks. –  Justicator Apr 23 '13 at 14:09
add comment

Alright, found a way to do the same with Table Valued Types

CREATE FUNCTION [dbo].[DateRange] 
    (
        @StartDate date,
        @EndDate date,
        @Location TVT.Location ReadOnly,
        @Device TVT.Device ReadOnly
    ) 
    RETURNS TABLE 
    AS
    RETURN 
    (
        SELECT *
        FROM MyTable
        WHERE Date < @EndDate 
              Date > @StartDate AND 
              (Location IN (SELECT Location FROM @Location) OR NOT EXISTS(SELECT 1 from @Location)) AND
              (Device IN (SELECT Device FROM @Device) OR NOT EXISTS(SELECT 1 FROM @Device))

    )
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.