Geek Answers Handbook

Stored Procedure Where Parameter Parameters

I have an ASP.net search page where a user can enter one or more search criteria. The page calls the stored procedure to query the MS SQL Server 2008 database. Db.

Part of the search criteria is a single day or date range. If the user supplies Date1, we are looking for a single date. If the user supplies Date1 and Date2, we are looking for a date range.

My problem is coding this logic in a stored procedure.

@Date1 datetime @Date2 datetime ..other search params...

So there are three conditions:

  • Both @ Date1 and @ Date2 are both null (the user does not search by date)
  • @ Date1 is not null and @ Date2 is null (user searches on one date)
  • @ Date1 is not null and @ Date2 is not null (user searches for date range)

I cannot figure out how to structure a WHERE clause to handle each of the three possible conditions.

I am familiar with ISNULL() and COALESCE()

Any advice or suggestions are welcome.

+4
source share
2 answers
 CREATE PROCEDURE BLABLABLA( @DATE1 DATETIME = NULL, @DATE2 DATETIME = NULL ) AS BEGIN SELECT COL1, COL2 FROM THE_TABLE WHERE THE_TABLE.DATETIMEFIELD BETWEEN ISNULL(@DATE1, THE_TABLE.DATETIMEFIELD) AND COALESCE(@DATE2, @DATE1, THE_TABLE.DATETIMEFIELD) END

Another choice, having lost some expressiveness, but probably using indexes, might be:

 CREATE PROCEDURE BLABLABLA( @DATE1 DATETIME = NULL, @DATE2 DATETIME = NULL ) AS BEGIN SELECT COL1, COL2 FROM THE_TABLE WHERE (THE_TABLE.DATETIMEFIELD >= @DATE1 OR @DATE1 IS NULL) AND (THE_TABLE.DATETIMEFIELD <= @DATE2 OR THE_TABLE.DATETIMEFIELD = @DATE1 OR (@DATE1 IS NULL AND @DATE2 IS NULL)) END
+4
source

You can try to create your SQL query as a string in SP, and then execute it, for example:

 ... declare @sql varchar(500) set @sql = 'select from myTable where 1=1' if(@Date1 <> null) set @sql = @sql + ' and date1 >= '+ @date1 if(@Date2 <> null) set @sql = @sql + ' and date2 <= '+ @date2 print(@sql) -- for debug exec(@sql)
+1
source

More articles:


All Articles