CODEWITHSUNDEEP
sql-serversql-server-2008where-clausecase-statement
Reshma
Reshma

Reputation: 1430

CASE statement within WHERE clause in SQL Server 2008

I want to write a CASE statement to find the data between two dates based on @sFRomDate empty or not. Given below query not working as per my requirement. Please help me to find a proper solution

SELECT 
    * 
FROM 
    tbl_emp_data 
WHERE
    CASE 
       WHEN @sFRomDate!='' 
          THEN SubmissionDate BETWEEN @sFRomDate AND DATEADD(DAY,1,@sToDate) 
       ELSE
          SubmissionDate = NULL
    END

Upvotes: 0

Views: 213

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

This might be the logic you were trying to implement. In the event that @sFRomDate be NULL or empty and SubmissionDate also be NULL or empty the record will be returned. Otherwise, the SubmissionDate will be checked to make sure it is within the range you defined.

SELECT *
FROM tbl_emp_data
WHERE (COALESCE(@sFRomDate, '') = '' AND
       COALESCE(SubmissionDate, '') = '') OR
      (COALESCE(@sFRomDate, '') <> ''     AND
       COALESCE(SubmissionDate, '') <> '' AND
       SubmissionDate BETWEEN @sFRomDate AND DATEADD(DAY, 1, @sToDate))

Upvotes: 2

user6638270
user6638270

Reputation:

If I understand what you want, I think it becomes simpler to follow the logic if you write:

IF @sFromDate = ''
BEGIN
SELECT * FROM tbl_emp_data where SubmissionDate=null
END
ELSE
BEGIN
SELECT * FROM tbl_emp_data where SubmissionDate between @sFRomDate and DATEADD(DAY,1,@sToDate)
END

But as @Tim Biegeleisen says, be careful with nulls, not just for @sFromDate but also @sToDate

Upvotes: 1

Related Questions