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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-11-30 : 08:09:14
|
| Matthew Hill writes "Background: I am developing a website using MS Visual Studio 2005 and SQL 2005 Express. I'm using SQL queries (not stored procedures yet). Problem: I have a query that I can't make work. I found the COALESCE function on your site and it solves part of the problem but not all.I have a variable '@filter_by_date' which is bound to a dropdownlist on the webpage. The dropdownlist's values are NULL, 7, 14, 30 which represent the number of days to subtract from today's date.If a user selects the NULL option the query should return all rows which is how my query is:SELECT * FROM t_jobs WHERE (created_date >= COALESCE(@filter_by_date, created_date))The problem arises when the user selects any of the other options. If the user selects 7, for example, then this value should be subtracted from the current date and used in the query to only show jobs created after that date. The query looks like this:SELECT * FROM t_jobs WHERE (created_date >= GETDATE() -@filter_by_date)I was using 36500 for the 'ALL' dropdown option value but it's a bit tacky and when there is a large number of rows in the table it's going to get slow.Is it possible to merge the two queries? I thought of trying to use an 'IF' in the 'WHERE' clause but I can't get it to work.Thank you for your time. Hope you can help." |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-11-30 : 08:53:08
|
| Boolean logic is your friend ...where (@filter_by_date is null) or (Created_Date >= GetDate() - @Filter_By_Date) |
 |
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-12-01 : 08:53:58
|
| You can use a CASE statement in combination with a WHERE clause, as I'll show in the following example:where [sub location] > (case @myvar when 3 then 'A' else 'F' end)In your case it is probably easiest to just use the OR as suggested, but I wanted to let you know in case you come across a special situation in the future. |
|
|
|
|
|
|
Can IF statements be used in WHERE clause?