Dynamic filter without Dynamic SQL? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Dynamic filter without Dynamic SQL?

Author

Topic

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2006-05-22 : 08:45:22

I think my brain is going to explode. I am trying to build a sp to return a resultset filtered as required by the user. @Filtertext is to be totally ignored if it is dbnull, otherwise it should add a "LIKE %@FilterText%" to the query. I can't use COALESCE cos this is operational.

The other problem I've got is that because I am using COALESCE, if one of the where clauses is true the row gets returned and it should only be returned if ALL are true.

Has anyone got an idea on how to approach this problem without dynamic SQL? I only need to filter on the parameters if they are NOT Null.

CREATE PROCEDURE Events_GetFiltered 
(
	@IsMember 		tinyint,
	@NoOfRows 		tinyint,
	@FilterText		varchar(128),
	@FilterMonth		tinyint,
	@FilterYear		smallint,
	@FilterListType		smallint,
)
 AS
SET Rowcount @NoOfRows

	SELECT UniqueID,Title, ShortDesc, StartDate, EndDate From Events 
	WHERE 
		PublicEvent=@IsMember and 
		datepart(month,getDate()) = COALESCE(@FilterMonth,datepart(month,getDate())) and
		datepart(year,getDate()) = COALESCE(@FilterYear,datepart(year,getDate())) and
		EventTypeID = COALESCE(@FilterListType, EventTypeID)
	ORDER BY StartDate asc

set rowcount 0
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-22 : 08:49:34

and (txt LIKE '%' + @FilterText + '%' or @FilterText is null)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-22 : 10:07:32

http://www.sommarskog.se/dyn-search.html

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-05-22 : 12:27:54

quote:
Originally posted by nr

and (txt LIKE '%' + @FilterText + '%' or @FilterText is null)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

you could also do:

and (txt LIKE '%' + ISNULL(@FilterText,'') + '%')

In general, if you can remove OR's from your criteria it will be a more efficient (not always, of course).

noamg
Posting Yak Master

215 Posts

Posted - 2006-05-23 : 06:25:25

contact me directly, I wrote a small article exactly about it.
NGraizer@NetVision.net.il

Noam Graizer

Kristen
Test

22859 Posts

Posted - 2006-05-23 : 06:32:43

Why not post your article here, or a link to it if its on the web somewhere?

Kristen

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2006-05-23 : 08:16:06

Argghhh. Brain has melted and still no working query :( This is a lot more difficult than it appeared, I'm tempted to go for 80 IF clauses......someone stop me!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-05-23 : 08:40:25

The truth of it is, if you are trying to truly write a completely dynamic stored procedure that accepts all kinds of criteria every which way, you are much better off dynamically building and executing a SQL statement.

It is important to separate your standard database operations using normal stored procedures from your "reporting" operations, which are often better off done dynamically. Please note that this doesn't mean that you should not use parameters once your build your SQL strings.

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2006-05-23 : 08:44:52

Here is what I have come up with so far. Can anyone see any scary caveats that I may have coming?


@IsMember 		tinyint,		/* 0=no, 1=yes */
	@NoOfRows 		tinyint,
	@FilterText		varchar(128),
	@FilterMonth		tinyint,
	@FilterYear		smallint,
	@FilterListType		smallint
)
 AS
SET Rowcount @NoOfRows

	SELECT UniqueID,Title, ShortDesc, StartDate, EndDate From Events 
	WHERE 
		PublicEvent=@IsMember and 
		(
			datepart(month,StartDate) = COALESCE(@FilterMonth,-1) 
		or 
			datepart(month,EndDate) = COALESCE(@FilterMonth,-1)
		) 
		and 
		(
			datepart(year,StartDate) = COALESCE(@FilterYear,-1)
		or 
			datepart(year,EndDate) = COALESCE(@FilterYear,-1)
		)
		and EventTypeID = COALESCE(@FilterListType, EventTypeID)
		and (Title LIKE '%' + ISNULL(@FilterText,'') + '%')
	ORDER BY StartDate asc
set rowcount 0
GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-05-23 : 08:48:24

Your logic looks completely wrong. Under what scenerio does DatePart(month, xx) return -1 ?? that will never match.

It might help if you write, in plain english, what your filter *should* be doing and how it should be using the parameters. Looking at existing code that doesn't work isn't an easy way to fix things, since it will involve guesswork on our part as to what the code should be doing.

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2006-05-23 : 09:11:11

@jsmith - Datepart month will never return -1, thats the point. If @filtermonth is not passed in I want false as the result of the OR operation so that the row is not included. The usual technique is to COALESCE(@FilterMonth, datepart(month,StartDate)) which ALWAYS includes the row.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-05-23 : 09:27:33

Your logic currently states that if @FilterMonth or @FilterYear is Null, *no* rows are ever returned (regardless of any other criteria, since you are using AND) because none of those expressions will ever evaluate to true for *any* row.

Is this what you want? Pass in NULL for @FilterMonth or @FilterYear and not get any rows back at all? Doesn't make much sense.

If you want the StartDate/EndDate to be *ignored* or not considered if @FilterYear/@FilterMonth are NULL, then that is a different story but not what you have written.

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2006-05-23 : 09:43:29

@jsmith - very good point! Doh! Got stuck in one testing mode. Thought it seemed a bit simple :( To fully explain what i have...

I have a control that displays:

1. EventTypeID Drop down
2. Month - Year drop downs (if one is passed, both are passed)
3. Search text

This control (should) filter the result. Problem is that not all the sections may be displayed. You may for instance only have EventType or searchtext or "Eventype AND Search text" filtering. Basically each row can be used or not. The sp above works when 1,2 and 3 are all enabled. Now I need to be able to get it to work when only one of them is enabled etc.

Sigh!

EDIT: Just to clarify I removed the date range for the mo to keep it a little simpler.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-05-23 : 09:46:22

I still am not following your date logic. You have a start date / end date in your data, and you are letting the user pick a month. What does this mean?

The way you had it, it looked like you were trying to say

"If a FilterYear/Month is passed, return all rows where StartDate is in the FilterYear/Month *or* EndDate is in the FilterYear/Month"

However, what if the start date is 1/1/2000 and the end date is 5/1/2000 and the filter year/month is "Feb 2000". Should that item be displayed?

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2006-05-23 : 09:58:07

The logic comes from the control. The user can only select 1 month. Some events may roll over a month end, hence to check for start and end date. Maybe starts on Fri 28th Feb but ends 2nd Mar. Heres a pic to show what I mean. Excuse the bad layout its all css and I haven't done it yet and these control are pre-pre alpha (ie 3 days old). The top bit is the event editor but the bottom is the filter and its filtered results follow.

Here we are saying "Show me all Breakfast events that are in may 2006"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-05-23 : 12:17:21

So no event will ever be more than 1 month long?

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-05-23 : 13:44:09

Ahh, RyanRandall's link to http://www.sommarskog.se/dyn-search.html covers what I wrote... should have read that closer first

I had originally used COALESCE without the ORs (was closer to JSmith's), but as mentioned in the article linked above, this will drop any records that have a null for the field being checked (not sure if nulls are allowed in this particular app, but I know this will affect others), so this example, after updates, looks remarkably similar to Sommarskog's (another option would have been to put COALESCE around the search fields as well as the parameters, but that gets even uglier).

Only non null parameters are applied to the filter, checks date span instead of specific month/year


	@IsMember 		tinyint,		/* 0=no, 1=yes */
	@NoOfRows 		tinyint,
	@FilterText		varchar(128) = null,
	@FilterMonth		tinyint = null,
	@FilterYear		smallint = null,
	@FilterListType		smallint = null
)
 AS
SET Rowcount @NoOfRows

	SELECT UniqueID,Title, ShortDesc, StartDate, EndDate 
	FROM Events 
	WHERE 	( PublicEvent = @IsMember )
	AND	(	( (datepart(month,StartDate) <= @FilterMonth) OR (@FilterMonth IS NULL) ) 
		 AND	( (datepart(year,StartDate)  <= @FilterYear)  OR (@FilterYear IS NULL)  )
		 AND	( (datepart(month,EndDate)   >= @FilterMonth) OR (@FilterMonth IS NULL) )
		 AND	( (datepart(year,EndDate)    >= @FilterYear)  OR (@FilterYear IS NULL)  )
		)
	AND	( (EventTypeID = @FilterListType)      OR (@FilterListType IS NULL) )
	AND	( (Title LIKE '%' + @FilterText + '%') OR (@FilterText IS NULL)     )
	ORDER BY StartDate asc
set rowcount 0
GO

Kristen
Test

22859 Posts

Posted - 2006-05-23 : 18:45:12

I have read that the optimiser will do a better job if the NULL test is before the field comparison e.g. change

( (datepart(month,StartDate) <= @FilterMonth) OR (@FilterMonth IS NULL) )

to

( (@FilterMonth IS NULL) OR (datepart(month,StartDate) <= @FilterMonth) )

The datepart thing is a bit of a killer, because it won't use any index on that column. You might be able to replace:

(datepart(year,StartDate) <= @FilterYear)

with a test against a real date (1st January of the FOLLOWING year)

StartDate <= @FilterYear_AsDate

and the same for the EndDate.

Not much that can be done for @FilterMonth in that regard, however it would probably be worth combining the tests for @FilterMonth and @FilterYear:


( (@FilterMonth IS NULL) 
   OR ( (datepart(month,StartDate) <= @FilterMonth) 
        AND (datepart(month,EndDate)   >= @FilterMonth) ) )

Kristen

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2006-05-24 : 03:31:19

Thanks for the input Kristen and samuel. I will try those optimisations. I'm new to the whole optimiser thing so am shooting in the dark at the mo.
Just gonna have a go at changing the whole date idea to 2 dates, will report back soon :)

Kristen
Test

22859 Posts

Posted - 2006-05-24 : 05:13:10

"Just gonna have a go at changing the whole date idea to 2 dates"

If you want "everything between these two dates" then a clustered index on that date column will be very efficient - but you can, obviously!, only have one clustered index so that may not fit other requirements.

My guess is that non-clustered index will be useful for "short-ish" resultsets - there will come a point where SQL Server says "Bugger this, too many lookups, I'll just scan the whole table instead"

Kristen

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2006-05-24 : 05:23:48

** WHOOSH ** Thats the sound of clustered indexes flying over my head Kristen

Must read more!

I approached the date issue from a different angle in the end. Rather than passing a month in, I decided to create two dates in the control before passing it to SQL server so that takes care of the date range issue aswell. All I do is when a user selects a month I get the first and last day of the month as 2 seperate dates and pass them in.

Just got to do some more testing to ensure it does what I expect then I'll be back


CREATE PROCEDURE Events_GetFiltered2
(
	@IsMember 		tinyint,		/* 0=no, 1=yes */
	@NoOfRows 		tinyint,
	@FilterText		varchar(128) = null,
	@FilterFromDate		datetime = null,
	@FilterToDate		datetime = null,
	@FilterListType		smallint = null
)
 AS
SET Rowcount @NoOfRows

	SELECT UniqueID,Title, ShortDesc, StartDate, EndDate 
	FROM Events 
	WHERE 	( PublicEvent = @IsMember )
	AND	(
			@FilterFromDate is null
			or
			( 
				(StartDate >= @FilterFromDate
				AND 
				StartDate <= @FilterToDate)
				OR
				(EndDate >= @FilterFromDate
				AND
				EndDate <= @FilterToDate)
			)
		)
	AND	(@FilterListType IS NULL OR EventTypeID = @FilterListType)
	AND	(@FilterText IS NULL OR Title LIKE '%' + @FilterText + '%')
	ORDER BY StartDate asc
set rowcount 0
GO

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources