Question about Dynamic WHERE clause without Dynamic SQL statement

I'm running into an issue while using coalesce to create a dynamic where
clause. To give some background, here is the article that got me started on
this:
http://www.sqlteam.com/item.asp?ItemID=2077
What I am trying to accomplish is a series of user configurable filters that
will ignore a parameter if its not selected. So, let's presume there are 2
possible filters: "Location" and "Assigned To" for equipment that is "signed
out" to individuals. If the user selects a Location but not a person
(assigned to), then it should query only on LocationID. This should work
vice versa. And it should also work if they choose not select either.
Table EquipmentAssignments:
EquipmentID | LocationID | AssignedToID
--
1 1 2
1 1 1
2 2 1
2 NULL 2
2 2 NULL
So, based on the above mentioned article:
CREATE PROC ListEquipmentAssignments(
@.locationID int = NULL,
@.assignedToID int = NULL)
AS
-- note: in actual implementation all ID fields are actually
UNIQUEIDENTIFIERs
-- but it easier to read by using INTs
SELECT EquipmentID
FROM EquipmentAssignments
WHERE LocationID = COALESCE(@.locationID, LocationID) AND
AssignedToID = COALESCE(@.assignedToID, AssignedToID)
GO
This works great, except when dealing with the NULL column values. Because
of how COALESCE works, it returns NULL in the instances of those 2 records,
which won't evaluate to TRUE (and thus those records don't get returned).
I want to stay away from dynamically building SQL in the stored procedure.
Does anyone know of any other technique that could address this issue?Unfortunately, that method is absolutely terrible when it comes to
performance -- it forces a table scan. Why don't you want to use dynamic
SQL for this? You'll get MUCH better results.
There are other, non-dynamic methods with less performance problems, but
dynamic SQL really works best for these situations.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Nate" <Nate@.discussions.microsoft.com> wrote in message
news:F09F7F73-D2F7-4A91-B596-B0A543E9AB49@.microsoft.com...
> I'm running into an issue while using coalesce to create a dynamic where
> clause. To give some background, here is the article that got me started
> on
> this:
> http://www.sqlteam.com/item.asp?ItemID=2077
> What I am trying to accomplish is a series of user configurable filters
> that
> will ignore a parameter if its not selected. So, let's presume there are
> 2
> possible filters: "Location" and "Assigned To" for equipment that is
> "signed
> out" to individuals. If the user selects a Location but not a person
> (assigned to), then it should query only on LocationID. This should work
> vice versa. And it should also work if they choose not select either.
> Table EquipmentAssignments:
> EquipmentID | LocationID | AssignedToID
> --
> 1 1 2
> 1 1 1
> 2 2 1
> 2 NULL 2
> 2 2 NULL
> So, based on the above mentioned article:
> CREATE PROC ListEquipmentAssignments(
> @.locationID int = NULL,
> @.assignedToID int = NULL)
> AS
> -- note: in actual implementation all ID fields are actually
> UNIQUEIDENTIFIERs
> -- but it easier to read by using INTs
> SELECT EquipmentID
> FROM EquipmentAssignments
> WHERE LocationID = COALESCE(@.locationID, LocationID) AND
> AssignedToID = COALESCE(@.assignedToID, AssignedToID)
> GO
> This works great, except when dealing with the NULL column values.
> Because
> of how COALESCE works, it returns NULL in the instances of those 2
> records,
> which won't evaluate to TRUE (and thus those records don't get returned).
> I want to stay away from dynamically building SQL in the stored procedure.
> Does anyone know of any other technique that could address this issue?|||http://www.sommarskog.se/dyn-search.html

> I'm running into an issue while using coalesce to create a dynamic where
> clause.|||IFNULL (location_id, 99) = COALESCE(@.my_location_id, IFNULL
(location_id, 99))
Don't do that. You cannot validate or verify such non-relational
proprietary extensions.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1147826363.210359.308410@.y43g2000cwc.googlegroups.com...
> IFNULL (location_id, 99) = COALESCE(@.my_location_id, IFNULL
> (location_id, 99))
Joe,
Can other SQL engines (other than SQL Server, that is) use an index to
satisfy this kind of query?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||>> Can other SQL engines (other than SQL Server, that is) use an index to sa
tisfy this kind of query? <<
DB2 has some optimizer stuff now that looks at COALESCE() and builds
several execution plans (up to 16 or 32 of them as I recall) per
procedure, then it picks one based on the input parameters. It looks
for search conditions that can be reduced to (a = a) by a NULL and
drops them from consideration.
I am not sure about Ingres.|||see my Zero to N column
http://www.sqlservercentral.com/columnists/sholliday/
"Nate" <Nate@.discussions.microsoft.com> wrote in message
news:F09F7F73-D2F7-4A91-B596-B0A543E9AB49@.microsoft.com...
> I'm running into an issue while using coalesce to create a dynamic where
> clause. To give some background, here is the article that got me started
on
> this:
> http://www.sqlteam.com/item.asp?ItemID=2077
> What I am trying to accomplish is a series of user configurable filters
that
> will ignore a parameter if its not selected. So, let's presume there are
2
> possible filters: "Location" and "Assigned To" for equipment that is
"signed
> out" to individuals. If the user selects a Location but not a person
> (assigned to), then it should query only on LocationID. This should work
> vice versa. And it should also work if they choose not select either.
> Table EquipmentAssignments:
> EquipmentID | LocationID | AssignedToID
> --
> 1 1 2
> 1 1 1
> 2 2 1
> 2 NULL 2
> 2 2 NULL
> So, based on the above mentioned article:
> CREATE PROC ListEquipmentAssignments(
> @.locationID int = NULL,
> @.assignedToID int = NULL)
> AS
> -- note: in actual implementation all ID fields are actually
> UNIQUEIDENTIFIERs
> -- but it easier to read by using INTs
> SELECT EquipmentID
> FROM EquipmentAssignments
> WHERE LocationID = COALESCE(@.locationID, LocationID) AND
> AssignedToID = COALESCE(@.assignedToID, AssignedToID)
> GO
> This works great, except when dealing with the NULL column values.
Because
> of how COALESCE works, it returns NULL in the instances of those 2
records,
> which won't evaluate to TRUE (and thus those records don't get returned).
> I want to stay away from dynamically building SQL in the stored procedure.
> Does anyone know of any other technique that could address this issue?|||Give us a scalable solution that won't do a TABLE SCAN because this is a
MICROSOFT SQL SERVER forum.
Keep the DB2 solution on the DB2 forum, the Ingres solution on the Ingres
forum.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1147830039.976788.281080@.j73g2000cwa.googlegroups.com...
> DB2 has some optimizer stuff now that looks at COALESCE() and builds
> several execution plans (up to 16 or 32 of them as I recall) per
> procedure, then it picks one based on the input parameters. It looks
> for search conditions that can be reduced to (a = a) by a NULL and
> drops them from consideration.
> I am not sure about Ingres.
>|||> IFNULL (location_id, 99) = COALESCE(@.my_location_id, IFNULL
> (location_id, 99))
This will do a TABLE SCAN or CLUSTERED INDEX SCAN which is effectively a
table scan.
Can I have a list of clients you have recommended this too and a contact
name there so I can go and fix your lousy solution, this won't scale, it
will cause all sorts of blocking on a real system that has
update/insert/delete activity.
Tidy code does not mean efficient code.
You've been told several times this won't work well on SQL Server so why
continue to post this horrible, inefficient solution?
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1147826363.210359.308410@.y43g2000cwc.googlegroups.com...
> IFNULL (location_id, 99) = COALESCE(@.my_location_id, IFNULL
> (location_id, 99))
>
> Don't do that. You cannot validate or verify such non-relational
> proprietary extensions.
>