Using IS NULL, IS NOT NULL in Case Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Using IS NULL, IS NOT NULL in Case Statement

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2014-01-08 : 12:57:51

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

38200 Posts

Posted - 2014-01-08 : 13:25:02

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/

evanburen
Posting Yak Master

167 Posts

Posted - 2014-01-08 : 13:52:36

Thank you, Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources