How best to vary operands in SELECT.

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)

How best to vary operands in SELECT.

Author

Topic

CactusJuice
Starting Member

46 Posts

Posted - 2005-03-24 : 11:39:42

I'm looking for an example of how to build a select that allows a user to choose operands. For example, a search form for products might have the following form fields.

Product Name:
Product SKU:
Vendor:
Price:
Date Ordered:

And for each I'd like to have pull downs to choose "equals", "greater than", "less than", or "is one of (comma delimitted string". Also, I do not want to use dynamic SQL.

So far, I have a SELECT that looks like


SELECT * FROM.....tables......WHERE....
(@pFilterVendor = '' OR pFilterVendor IN (SELECT IntValue FROM dbo.CsvToInt(@pFilterVender)))
--CsvToInt is UDF to break apart a comma-demited string

The above logic takes care of blank, equal-to or comma-delimited entries...and avoides dynamic SQL. I'm not sure how to build in the greater/less than logic. Do I have to use a CASE statement?

thanks,

Cameron

CactusJuice
Starting Member

46 Posts

Posted - 2005-03-24 : 12:26:21

Well, not sure if this is the most elegant way but I can add a new parameter @pFilterTest in this case. Then if I detect the "greater than" DropDrown was selected I pass in @pFilterTest as "1". I would need to do this again for a "less than". Multiply the search options though and the SQL code will grow quickly. So if anyone has any ideas or artilce links, would be appreciated. Thanks.


(@pFilterVendor = '' OR Vendor IN (SELECT IntValue FROM dbo.CsvToInt(@pFilterVender))
OR (@pFilterTest = 1 AND Vendor > @pFilterVendor))

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-24 : 12:54:45

if you allow the user to build somewhat complex criteria themselves, then your app should just generate the SQL dynamically. While not ideal, in these situations you are much better off with dynamic SQL in terms of performance and clarity. Incorporating every possible way the criteria can be set with lots of OR's and CASE statements in your WHERE clause will end up being quite a mess, and SQL will ultimately not be able to generate an efficient execute plan or make use of any indexes.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources