Active/dynamic SELECT
-
Right, hello eveyone
New to MS-SQL, I normally use MYSQL and the queries I use are fairly straight forward.
I have potentially got a new job to do with MS-SQL and it calls for a complicated select command based on a form being filled out.
I will keep it simple for the purposes of this question
I want to select customers from a customer table based on upto 3 criteria from a form
the criteria are postcode, gender and age
the form will have a text box for postcode, a tick box for m and f and then a drop down box for age with 2 text boxes
the age drop down will be
1/ no selection
2/ Over - only uses the first text box
3/ Under - only uses the second text box
4/ between - uses both text boxes.
In order to keep the SELECT as simple as possible I don't want to include any of the criteria unless I have to so if both m and f were ticked (the default) then the SELECT query does not contain a WHERE gender = 'm' OR gender = 'f', or if the age selection was 'no selection' then there would be nothing in the query to indicate age.
Because this problem is going to be expanded to include a large number of clauses I don't want to use lots of IF statemenst to generate the WHERE clause before putting it into the SELECT.
I was looking at CASE but am unsure if this is the best way forward.
Any suggestions or pointers welcome
I'm not talking about a couple of thousand records here, its a significant number, and i may have to cross select into different tables at times
-
If I understand right, have a look at this:
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quick look
that looks like just the job, cheers!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply