CODEWITHSUNDEEP
sql-serverstored-procedures
Sherry
Sherry

Reputation: 25

Where Clause with Conditions in SQL Server

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:

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)

Upvotes: 1

Views: 112

Answers (3)

dfundako
dfundako

Reputation: 8324

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)

Upvotes: 0

M.Ali
M.Ali

Reputation: 69584

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

Upvotes: 2

Charles Bretana
Charles Bretana

Reputation: 146603

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)

Upvotes: 0

Related Questions