Conditional Where clause - resolved

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 2005 Forums

Transact-SQL (2005)

Conditional Where clause - resolved

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-08 : 15:58:05

Ok, having a bit of trouble with this....

	WHERE	((a.ClientID = @clientID OR a.ClientID = '' OR a.ClientID IS NULL)
		OR (a.ImportTypeID = @type OR a.ImportTypeID = '' OR a.ImportTypeID IS NULL)
		OR (DATEDIFF(day, a.CompletedDate, GETDATE()) <= @days 
			OR DATEDIFF(day, a.CompletedDate, GETDATE()) IS NULL 
			OR DATEDIFF(day, a.CompletedDate, GETDATE()) = '')
	)	AND a.CompleteStatus = 1

The problem is the DATEDIFF.
If @days is not used, it pulls back extra rows for the @type.

I tested it using this: exec pcGet_Search '', 4, ''
Suggestions?

Thanks,

Zath

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-08 : 16:03:48

Just did this real quick before a meeting, but I think this will work for you:

WHERE	
	(
		(a.ClientID = @clientID OR a.ClientID = '' OR a.ClientID IS NULL)
		OR (a.ImportTypeID = @type OR a.ImportTypeID = '' OR a.ImportTypeID IS NULL)
		OR 
		(
			DATEDIFF(day, a.CompletedDate, GETDATE()) <= @days 
			OR @days IS NULL 
		)
	)	AND a.CompleteStatus = 1

EDIT: You should pass NULL not an empty string.

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-08 : 16:06:07

No, sorry, didn't work...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-08 : 17:33:21

How do you want the optional parameters handled? Here is another guess:

-- Make the Stored Procuedure call
exec pcGet_Search NULL, 4, NULL


WHERE	
	(
		(a.ClientID = @clientID OR a.ClientID IS NULL)
		OR (a.ImportTypeID = @type OR a.ImportTypeID IS NULL)
		OR (DATEDIFF(day, a.CompletedDate, GETDATE()) <= @days OR @days IS NULL)
	)	
	AND a.CompleteStatus = 1

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-09 : 08:53:28

Ok, this is not working.

I changed some things as well and simplified it and it still won't work. To test, I set up a new query window with this:

	DECLARE @days		INT
	DECLARE @type		INT
	DECLARE @clientID	INT

	SET @days	 = NULL
	SET @type	 = 4
	SET @clientID = 17

SELECT * FROM ImportAlert a

WHERE	
	(
		(a.ClientID IS NULL OR a.ClientID = @clientID)
		AND 
		(a.ImportTypeID IS NULL OR a.ImportTypeID = @type)
		--AND (DATEDIFF(day, a.CompletedDate, GETDATE()) <= @days OR @days IS NULL)
	)	
	AND a.CompleteStatus = 1

I commented out the DATEDIFF for now just to test the other two.
Above how it is returns 8 rows.
If I set @type = NULL, it returns 0, yes, ZERO rows when it should return 8 rows as well.

And just for more info, here is the full sproc:

CREATE PROCEDURE dbo.pcGet_ImportAlertSearch
		
	@days		INT = NULL,
	@type		INT = NULL,
	@clientID	INT = NULL

AS
BEGIN

	SET NOCOUNT ON

	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	
	SELECT a.ImportAlertID, a.ClientID, b.ImportType, a.ProcessedBy, c.Name, a.ProcessDate, 
					 
			a.CompleteStatus, a.CompletedBy, a.CompletedDate

			
	FROM ImportAlert a
	INNER JOIN ImportType b ON a.ImportTypeID = b.ImportTypeID
	INNER JOIN Client c ON a.ClientID = c.pk_id



WHERE	
	(
		(a.ClientID IS NULL OR a.ClientID = @clientID)
		AND (a.ImportTypeID IS NULL OR a.ImportTypeID = @type)
		AND (DATEDIFF(day, a.CompletedDate, GETDATE()) <= @days OR @days IS NULL)
	)	
	AND a.CompleteStatus = 1
	

	ORDER BY a.CompletedDate

	SET TRANSACTION ISOLATION LEVEL READ COMMITTED

	SET NOCOUNT OFF

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-09 : 09:33:42

I found it!

It should have been this:

@clientID IS NULL OR a.ClientID = @clientID

NOT this

a.ClientID IS NULL

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-09 : 09:51:05

That's what I thought but it sounded to me like there are relations between @type and @days.
Maybe I misunderstood...
Now I see - the main problem was using OR instaed of AND!

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources