SQL Server stored procedure optional parameters, include all if null

Question

I have this stored procedure:

ALTER PROCEDURE [dbo].[GetCalendarEvents]
    (@StartDate datetime, 
     @EndDate datetime, 
     @Location varchar(250) = null)
AS
BEGIN
    SELECT * 
    FROM Events 
    WHERE EventDate >= @StartDate 
      AND EventDate <= @EndDate 
      AND (Location IS NULL OR Location = @Location)
END

Now, I have the location parameter, what I want to do is if the parameter is not null then include the parameter in where clause. If the parameter is null I want to completely ignore that where parameter and only get the result by start and end date.

Because when I'm doing this for example:

EXEC GetCalendarEvents '02/02/2014', '10/10/2015', null

I'm not getting any results because there are other locations which are not null and since the location parameter is null, I want to get the results from all the locations.

Any idea how can I fix this?

Dbloch · Accepted Answer · 2015-04-03 18:15:07Z

2

ALTER PROCEDURE [dbo].[GetCalendarEvents]
( @StartDate DATETIME,
  @EndDate DATETIME,
  @Location VARCHAR(250) = NULL
)
AS
BEGIN
    SELECT  *
    FROM    events
    WHERE   EventDate >= @StartDate
            AND EventDate <= @EndDate
            AND Location = ISNULL(@Location, Location )
END

If a NULL column is a possibility, then this would work.

ALTER PROCEDURE [dbo].[GetCalendarEvents]
( @StartDate DATETIME,
  @EndDate DATETIME,
  @Location VARCHAR(250) = NULL
)
AS
BEGIN
    IF ( @loc IS NULL )
        BEGIN
            SELECT  *
            FROM    events
            WHERE   EventDate >= @StartDate
                    AND EventDate <= @EndDate
        END
    ELSE
        BEGIN
            SELECT  *
            FROM    events
            WHERE   EventDate >= @StartDate
                    AND EventDate <= @EndDate
                    AND Location = @Location
        END
END

As having an 'OR' clause should be reasonably avoided due to possible performance issues.

edited Apr 3, 2015 at 18:15

answered Apr 3, 2015 at 15:38

Dbloch

2,4061 gold badge16 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Laziale Over a year ago

Thanks, I'll accept your answer in 10 minutes. That did it. Have a good weekend

George Mastros Over a year ago

This will fail if the Location column in the events table can contain null. The advice from @TT. is better.

TT. · Accepted Answer · 2015-04-03 15:43:34Z

0

The part in the WHERE clause should then read

AND (@Location IS NULL OR Location=@Location)

answered Apr 3, 2015 at 15:43

TT.

16.2k6 gold badges53 silver badges95 bronze badges

Comments

Mike Laren · Accepted Answer · 2015-04-04 01:48:50Z

0

Try this

 SELECT * 
    FROM Events 
    WHERE EventDate >= @StartDate 
      AND EventDate <= @EndDate 
      AND Location = Case When LEN(@Location) > 0 Then  @Location Else Location End

edited Apr 4, 2015 at 1:48

Mike Laren

8,23618 gold badges53 silver badges72 bronze badges

answered Apr 3, 2015 at 17:28

Koras

743 bronze badges

Comments

oshan2csd · Accepted Answer · 2015-04-04 05:01:35Z

It can be easily done with a dynamic sql query.

ALTER PROCEDURE [dbo].[GetCalendarEvents]
(@StartDate datetime, 
 @EndDate datetime, 
 @Location varchar(250) = null)
AS
BEGIN
   DECLARE @SQL NVARCHAR(MAX);
   DECLARE @PARAMETER_DEFIINITION NVARCHAR(MAX);
   DECLARE @WHERE_PART NVARCHAR(MAX);

   SET @PARAMETER_DEFIINITION =' @StartDate DATETIME, @EndDate DATETIME, @Location VARCHAR(250) '

   SET @SQL ='SELECT * 
              FROM Events 
              WHERE EventDate >= @StartDate 
              AND EventDate <= @EndDate '

   IF @Location IS NOT NULL
   BEGIN
       SET @WHERE_PART = ' AND Location  = @Location '
   END    

   SET @SQL = @SQL + @WHERE_PART
   EXEC SP_EXECUTESQL @SQL, @PARAMETER_DEFIINITION, @StartDate, @EndDate, @Location
END

Query will be dynamically created according to the parameters. In here if @location is null then it will not add to the where part.

If you want more on writing dynamic queries please refer this article. http://codingpulse.blogspot.com/2015/02/dynamic-sql-in-stored-procedure-part-1.html

Collectives™ on Stack Overflow

SQL Server stored procedure optional parameters, include all if null

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related