1

I have a SQL stored procedure for a search page on a website where a user can search records by, and between, two date fields: initiatedDateStart and initiatedDateEnd. The logic for the SQL query should be as follows:

  • If the startDate has a value, but the endDate is empty, or equal to the startDate, return records that were created ON the startDate.
  • If the startDate has a value, and the endDate value is greater than
    the startDate, return records that were created BETWEEN the two
    dates.

Since I'm not a SQL expert, my stored procedure is not very elegant or clever, but it does return the records I expect it to, except for the date fields. I have researched for this, but the syntax and logic is stumping me. Here is a snippet of my stored procedure. If a value is entered in the initStartDate texbox, but the initEndDate is empty, this returns ALL the records created AFTER the start date. I appreciate any and all help on this. 

DECLARE @initStartDate datetime = NULL DECLARE @initEndDate datetime = NULL SELECT DISTINCT d.[DCRId], d.[Title], d.[FiscalYear] FROM [dbo].[tblDCR] d LEFT OUTER JOIN [dbo].[tblWorkflow] orig ON (d.[DCRId] = orig.[DCRId] AND orig.StepName = 'Draft') WHERE 1 = 1 AND (orig.BadgeDate >= @initStartDate OR @initStartDate IS NULL) AND (orig.BadgeDate <= @initEndDate OR @initEndDate IS NULL)
Improve this question

3 Answers 3

Reset to default
2

The best way I can think of to handle this would be to use dynamic sql 

DECLARE @initStartDate datetime = NULL; DECLARE @initEndDate datetime = NULL; Declare @Sql NVARCHAR(MAX); SET @Sql = N'SELECT DISTINCT d.[DCRId], d.[Title], d.[FiscalYear] FROM [dbo].[tblDCR] d LEFT OUTER JOIN [dbo].[tblWorkflow] orig ON (d.[DCRId] = orig.[DCRId] AND orig.StepName = ''Draft'') WHERE 1 = 1 ' + CASE WHEN @initStartDate IS NOT NULL THEN N' AND orig.BadgeDate >= @initStartDate ' ELSE N'' END + CASE WHEN @initEndDate IS NOT NULL THEN N' AND orig.BadgeDate <= @initEndDate ' ELSE N'' END Exec sp_executesql @Sql ,N'@initStartDate datetime, @initEndDate datetime' ,@initStartDate ,@initEndDate
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

M.Ali, thank you. I did consider dynamic SQL, but I have never used it and the stored procedure is a bit more complicated than just this bit, so I didn't try to implement dynamic SQL. How would I include a CASE without dynamic SQL?
0

I think a solution to your problem would be to assign the current date to the end parameter when it is left NULL. By doing this, a user can enter no parameters and get the current date values, enter just the start date and get data on the start date, or enter the end date and get data between the current date and the end date. As a final catch, you can tell the procedure to return only today's data if for some reason a user puts a higher start date than end date.

CREATE PROCEDURE test_proc @initStartDate date = null, @initEndDate date = null AS IF @initStartDate IS NULL AND @initEndDate IS NULL BEGIN SET @initStartDate = (SELECT GETDATE()) SET @initEndDate = @initStartDate END ELSE IF @initStartDate IS NULL AND @initEndDate IS NOT NULL BEGIN SET @initStartDate = @initEndDate END ELSE IF @initEndDate IS NULL AND @initStartDate IS NOT NULL BEGIN SET @initEndDate = @initStartDate END IF @initStartDate > @initEndDate BEGIN SET @initStartDate = (SELECT GETDATE()) SET @initEndDate = @initStartDate END SELECT DISTINCT d.[DCRId], d.[Title], d.[FiscalYear] FROM [dbo].[tblDCR] d LEFT OUTER JOIN [dbo].[tblWorkflow] orig ON (d.[DCRId] = orig.[DCRId] AND orig.StepName = 'Draft') WHERE orig.BadgeDate BETWEEN @initStartDate AND @initEndDate GO

My modified SQL

DECLARE @initStartDate date = '10/21/15' DECLARE @initEndDate date = NULL IF @initStartDate IS NULL AND @initEndDate IS NOT NULL BEGIN SET @initStartDate = @initEndDate END ELSE IF @initEndDate IS NULL AND @initStartDate IS NOT NULL BEGIN SET @initEndDate = @initStartDate END SELECT DISTINCT d.[DCRId], d.[Title], d.[FiscalYear] FROM [dbo].[tblDCR] d LEFT OUTER JOIN [dbo].[tblWorkflow] orig ON (d.[DCRId] = orig.[DCRId] AND orig.StepName = 'Draft') WHERE 1 = 1 AND (orig.BadgeDate BETWEEN @initStartDate AND @initEndDate) OR (@initStartDate IS NULL OR @initEndDate IS NULL)
Improve this answer

5 Comments

dfundako, thank you; I understand where you are headed with this. The problem is that the date fields may very well be null if the user does not want to search by date. So the test would need to be if there is a value in either one of the date fields, then return records created on that specific date. If both date fields have value, then the result would be BETWEEN the dates.
I have edited the query to look at records only on one date if only one date is provided for either the initStart or initEnd.
dfundako, I added my modified SQL in your post, and it works as long as the start date and end date are not the same. For some reason the BETWEEN function is not returning the records if the dates are the same. The dates may also both be null; in that case the user doesn't want to search by date and so nothing should be returned for that field.
I believe the reason your BETWEEN is not working is because you have your parameters as DATETIME instead of DATE. When you run a BETWEEN statement that is looking for records BETWEEN 2015-10-21 00:00:00 and 2015-10-21 00:00:00, it will not return anything. But if you run it as BETWEEN 2015-10-21 AND 2015-10-21, you will get all results on 2015-10-21 regardless of the additional timestamp.
thank you for sticking with me on this, dfundako, with your help I was finally able to get the whole stored procedure working.
0

but what if the @initStartDate does not have a value ? assuming you want it to go all the way back if no startDate criteria, then, pass the date criteria as Nullable parameters, as you are: then, (assuming the database field is date only with no time), use the following where clause:

 where orig.BadgeDate Between Coalesce(@initStartDate, orig.BadgeDate) and Coalesce(@initEndDate, @initStartDate, orig.BadgeDate)
Improve this answer

2 Comments

Thank you, Charles. Since this stored procedure returns the results of a search web page where a user may, or may not, enter any values in a search field, null values are being passed in for any unselected options. The date fields may very well be null. But the orig.BadgeDate value in the database will not be null. So Coalesce will always return a value even if the user does not want to search by date, is that true?
Coalesce will return the first argument that is NOT null, so yes, if the last argument is the orig.BadgeDate, then the coalesce will always return a non-null value, but this is, effectively, the same as not having a boundary on that end, as orig.BadgeDate will always be equal to orig.BadgeDate,

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.