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 |
|
calvinfoo
Posting Yak Master
129 Posts |
 Posted - 2011-03-28 : 23:29:07
|
| Dear Gurus,I am trying to do:If Date_from and Date_to is specified, then retrieve the RecordDate in between it; else get all records.So what I do is test to ensure both Date_from and Date_to is not null, then use it as select condition, apart from other conditionsI tried the below, but it keeps saying "Incorrect syntax near the keyword 'BETWEEN'"I am running out of ideas.SELECT * FROM myTable WHERE 0=0AND CASE WHEN ((@Date_From IS NOT NULL) AND (@Date_To IS NOT NULL)) THENCONVERT (varchar(10), RecordDate, 120) BETWEEN @Date_from AND @Date_to ELSE 0=0 END-- Other conditions follow....AND (@Value1 IS NULL OR Value1 = @Value1)AND (@Value2 IS NULL OR Value2 = @Value2)-- etc... |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-03-28 : 23:56:03
|
you have got the case when syntax wrong. You can't have the case when returning a condition. It should return a value.According to the Books On Linequote:
CASE Evaluates a list of conditions and returns one of multiple possible result expressions
SELECT *FROM myTableWHERE ( (@Date_From IS NOT NULL AND @Date_To IS NOT NULL AND RecordDate BETWEEN @Date_From AND @Date_To) OR (@Date_From IS NULL) OR (@Date_To IS NULL) ) Are you forming the query dynamically ? Why do you need the 0 = 0 in the WHERE clause ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
calvinfoo
Posting Yak Master
129 Posts |
Posted - 2011-03-29 : 00:12:39
|
| I see. Thanks for the tips.The 0=0 I put there is somehow I tested I can't empty between ELSE END. so I put 0=0 as dummy value... I must be wrong... I am still very new to SP ProgrammingYes, I am trying to create a dynamic Query, which is piece of cake if I use ASP... but this time i need to put it in SP as I need the Query to be able to run on Crystal Report too instead of just ASP. :(I'll test and let you know on your suggestion (I am sure it works). |
|
|
|
calvinfoo
Posting Yak Master
129 Posts |
Posted - 2011-03-29 : 00:16:31
|
| If you are refering to the "WHERE 0=0", yes, I am kinda used to put that as my ASP habit... I always put 0=0 when I use ASP to check and accept user input in the search criteria... |
|
|
|
calvinfoo
Posting Yak Master
129 Posts |
Posted - 2011-03-29 : 00:26:43
|
| Tested, it works flawlessly. Amazing... I still trying to digest and understand your suggested syntax... |
|
|
|
|
|
|
|
|
WHERE CASE questions