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 |
|
BCrowe
Starting Member
23 Posts |
 Posted - 2004-08-26 : 08:56:36
|
| I am relatively new to SQL and am hoping someone out there with more experience can point me in the right direction.I am trying to write an sp to filter out records based on parameter criteria. Normally I would just create the SQL string in the application and pass it in as commandtext. However, the designers of the DB, in their infinite wisdom, decided to store some numeric values in text as fractions i.e. "23 7/8" and I want to filter out the records server-side. I created a udf to parse the text into a decimal value (having problems getting the query analyzer to let me test it but that's a separate issue).To make a short story long, I am trying to create the WHERE clause to allow for various comparison operators and am having trouble.WHERE...CASE WHEN @OrderDateMod = '*' THEN [myTable].OrderDate = [myTable].OrderDate WHEN @OrderDateMod = '>' THEN [myTable].OrderDate >= @OrderDate1 WHEN @OrderDateMod = '<' THEN [myTable].OrderDate <= @OrderDate1 WHEN @OrderDateMod = '=' THEN [myTable].OrderDate = @OrderDate1 WHEN @OrderDateMod = 'B' THEN [myTable].OrderDate BETWEEN @OrderDate1 AND @OrderDate2END AND...If anyone can help me out or redirect me to a more efficient way to accomplish this I would appreciate it.Thanks,BCrowe |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-08-26 : 09:54:34
|
| You would be better off creating the SQL statement as a string and executing it using sp_executeSQL. Check out BOL for more info |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-08-26 : 09:56:28
|
I feel your pain. You could use some nifty boolean logic to help you with this. Where's Dr. Cross Join, he's usually very good at explaining this sort of thing. Anyway, I mean something like this:WHERE ...AND((@OrderDateMod = '>' AND OrderDate >= @OrderDate) OR (@OrderDateMod <> '>')) AND((@OrderDateMod = '<' AND OrderDate <= @OrderDate) OR (@OrderDateMod <> '<')) AND((@OrderDateMod = '=' AND OrderDate = @OrderDate) OR (@OrderDateMod <> '=')) AND... Does this help you?OS |
|
|
|
BCrowe
Starting Member
23 Posts |
Posted - 2004-08-26 : 10:48:11
|
| Thank you for responding so quickly. I was hoping I could just use the CASE statement but figured I would have to resort to building the query string from scratch.BCrowe |
|
|
|
|
|
|
CASE Statement in WHERE Clause