| Author |
Topic |
|
CactusJuice
Starting Member
46 Posts |
 Posted - 2005-03-31 : 13:29:18
|
| [code]SELECT ... FROM ... WHERE(@pFilterCSV = '' OR StatusInDays IN (SELECT IntValue FROM dbo.CsvToInt(@pFilterCSV)) OR TransferStatus <> 'OPEN' OR (@pOver = 2 AND StatusInDays > @pFilterCSV))[/code]The above worked fine...until I added in the piece in red. Now it only works if a single number is entered. If a string of comma demitted numbers is entered then throws an error "error converting the varchar value '2,12,20,25' to a column of data type smallint."(CsvToInt is a UDF that splits up a comma delimitted string.) |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-03-31 : 13:35:08
|
| StatusInDays > @pFilterCSVWhat is that supposed to be doing? you understand that if @pFilterCSV is equal to '1,2,3' and StatusInDays is an integer (say, 5) then this becomes:5 > '1,2,3'What is that supposed to be checking or returning? You are comparing a numeric value to a string of numbers.Do you want to ensure that the value StatusInDays is greater than all values in the @pFilterCSV string if it is parsed into a set of integers?- Jeff |
 |
|
|
CactusJuice
Starting Member
46 Posts |
Posted - 2005-03-31 : 13:52:56
|
It returns a list of records where the StatusInDays column value (type int) is equal to whatever number(s) the user enters..."1,2,3" for example returns every record where StatusInDays column is 1, 2 or 3.The user normally enters something like "1,2,3" for @pFilterCSV. But now they want to be able to enter just one value and do a "greater than" search. So I created a second parameter named @pOver. If @pOver = 2 then I know the search is supposed to be a greater than search. If @pOver = anything else I don't want to do a greater than search. I was hoping to have @pOver function like a switch. In otherwords, ignore the red part unless it equals 2. (If I get it working then there's a @pFilter2CSV and @pOver2 params as well, 1/2 dozen all together.)Table is wider but in general looks like:PK | StatusInDays (tinyint) | TransferStatus (varchar) | Col4 | Col5 1 3 HOLD .. ..2 3 OPEN .. ..3 5 OPEN .. ..4 10 OPEN .. ..5 2 HOLD .. ..6 5 WAIT .. .. |
|
|
|
CactusJuice
Starting Member
46 Posts |
Posted - 2005-03-31 : 13:54:06
|
Yes, I think this would work as one way to get the job done.quote:
Originally posted by jsmith8858
Do you want to ensure that the value StatusInDays is greater than all values in the @pFilterCSV string if it is parsed into a set of integers?
|
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-03-31 : 13:58:17
|
| If that's what you want, then specify it:StatusInDays > (Select Max(IntValue) from dbo.CsvToInt(@pFilterCSV))Makes some sense, right?Though to be honest, I would set up two different SELECT's and use an IF statement to deteremine which one to execute since they are so differnt in the way they are making use of the parameters passed in. Building one big WHERE clause that allows for too many possibilities will be inefficient and hard to read/maintain.- Jeff |
|
|
|
CactusJuice
Starting Member
46 Posts |
Posted - 2005-03-31 : 14:28:48
|
Ah yes MAX! Thanks. This snippet works great!SELECT ... FROM ... WHERE(@pFilterCsv = '' OR (@pOver = 1 AND StatusInDays IN (SELECT IntValue FROM dbo.CsvToInt(@pFilterCsv))) OR TransferStatus <> 'HOLD' OR (@pOver = 2 AND StatusInDays > (Select Max(IntValue) from dbo.CsvToInt(@pFilterCsv))))AND(@pFilter2Csv = '' OR (@pOver2 = 1 AND StatusInDays IN (SELECT IntValue FROM dbo.CsvToInt(@pFilter2Csv))) OR TransferStatus <> 'OPEN' OR (@pOver2 = 2 AND StatusInDays > (Select Max(IntValue) from dbo.CsvToInt(@pFilter2Csv))))AND(@pFilter3Csv = '' OR (@pOver3 = 1 AND StatusInDays IN (SELECT IntValue FROM dbo.CsvToInt(@pFilter3Csv))) OR TransferStatus <> 'WAIT' OR (@pOver3 = 2 AND StatusInDays > (Select Max(IntValue) from dbo.CsvToInt(@pFilter3Csv))))AND(@pFilter4Csv = '' OR (@pOver4 = 1 AND StatusInDays IN (SELECT IntValue FROM dbo.CsvToInt(@pFilter4Csv))) OR TransferStatus <> 'FAIL' OR (@pOver4 = 2 AND StatusInDays > (Select Max(IntValue) from dbo.CsvToInt(@pFilter4Csv)))) I agree with you the parameters are being used in different ways. Though, regarding using an "IF", I don't think that's possible. I don't think I can put "IF" in the WHERE clause...at least I don't know how  |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-03-31 : 14:41:40
|
no, that's not what I meant. T-SQL allows you to use an IF statment to control the flow of a stored procedure based on conditions:IF @pOver=1 SELECT .... FROM .. WHERE ...ELSE IF @pOver =2 SELECT .... FROM .. WHERE ...ELSE IF @pOver = 3 SELECT .... FROM .. WHERE .....etc... - Jeff |
|
|
|
CactusJuice
Starting Member
46 Posts |
Posted - 2005-03-31 : 14:50:16
|
| OK I understand...basically vary it on a "top level" basis. I guess I could probably do a CASE statement too since there will eventually be about 6-8 IFs. OK I'll give that a try. The bummer is that QA runs what I have in about 2 seconds so it will be very hard to determine if any performance gain/loss from using IFs. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-03-31 : 14:53:19
|
| >>I guess I could probably do a CASE statement too since there will eventually be about 6-8 IFsRemember that CASE doesn't work the same way as an IF. CASE is a function that returns an expression; IF is a control-of-flow statement. Good luck. Remember that it's only important in terms of the time things take to execute, but also in how clear your code looks and how easy it is to maintain.I'd take a 5% performance loss if my code ends up being much shorter and clearer and easier to read/maintain (in most cases, anyway)....- Jeff |
|
|
|
|
Error converting varchar to int