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.
| Author |
Topic |
|
Chrome Orange
Starting Member
6 Posts |
 Posted - 2004-11-05 : 07:33:43
|
| Right, hello eveyoneNew 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 formthe criteria are postcode, gender and agethe 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 boxesthe age drop down will be 1/ no selection2/ Over - only uses the first text box3/ Under - only uses the second text box4/ 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 |
|
|
ts_abbott@hotmail.com
Starting Member
36 Posts |
Posted - 2004-11-05 : 07:55:41
|
| try this.SELECT c.*FROM Customers cWHERE (@postcode IS NULL OR c.PostCode=@PostCode)AND (@gender IS NULL OR c.Gender=@gender)AND (@age IS NULL OR c.Age=@Age) |
 |
|
|
|
|
|
dynamic/active SELECT query