SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Using IS NULL, IS NOT NULL in Case Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

evanburen
Posting Yak Master

155 Posts

Posted - 01/08/2014 :  12:57:51  Show Profile  Reply with Quote
I don't understand why this doesn't work. It doesn't like the IS NULL and IS NOT NULL in the Case statement. I know it shouldn't be

DateResolved = IS NULL

which doesn't make any sense. What's a better way of doing this? Thanks


CREATE PROCEDURE p_MyPCAComplaints
	 @UserID varchar(50) = NULL
	,@DateResolved int = NULL
AS
BEGIN

	SET NOCOUNT ON;

	SELECT
		 ComplaintID
		,DateReceived
		,BorrowerNumber
		,BorrowerName
		,DueDate
		,DateResolved
	FROM
		PCAComplaints
	WHERE
		UserID = @UserID
		AND DateResolved = 
		CASE 
		WHEN @DateResolved = 1 THEN IS NOT NULL
                WHEN @DateResolved = 0 THEN IS NULL
                ELSE IS NULL
        END

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 01/08/2014 :  13:25:02  Show Profile  Visit tkizer's Homepage  Reply with Quote
WHERE
UserID = @UserID
AND ((@DateResolved = 1 AND DateResolved IS NOT NULL) OR (@DateResolved = 0 AND DateResolved IS NULL))


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

evanburen
Posting Yak Master

155 Posts

Posted - 01/08/2014 :  13:52:36  Show Profile  Reply with Quote
Thank you, Tara
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000