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 |
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
 Posted - 2008-04-15 : 14:09:42
|
| GreetingsI have a stored procedure where I want to use a good boolean algebra based on a certain parameter. My parameter might come in NULL or varchar(50) of concatenated values. How do I got about doing this in the WHERE clause?IF @parm IS NULL Table.WorkOrderNum = COALESCE(@parm, Table.WorkOrderNum )ELSE Table.WorkOrderNum IN (@parm) meaning Table.WorkOrderNum IN ('1','2','3')Is this stretching or do i have a good case for optimization using a good boolean algebra Thank you |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-15 : 14:15:21
|
| Try:-((@parm IS NULL) OR (',' + @parm + ',' LIKE '%,''' + Table.WorkOrderNum + ''',%')) |
 |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2008-04-15 : 14:33:16
|
| Excellent! Yuo gave me a great lead and I am using the followingAND (@WorkOrderNum IS NULL OR (WONumber IN (@WorkOrderNum)))Thanks! |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2008-04-15 : 14:46:05
|
| Ok not so excellent what I did. Yisakh16 is there a way I could use the IN syntax rather than the LIKE syntax ?Thanks |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-15 : 15:30:37
|
www.sommarskog.se/dynamic_sql.html
E 12°55'05.25"N 56°04'39.16" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-04-16 : 03:18:54
|
| http://www.sommarskog.se/arrays-in-sql.htmlMadhivananFailing to plan is Planning to fail |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2008-04-16 : 12:06:56
|
| nice stuff already using it! only limitation with these "arrays-in-sql" is that they expect/assume the parm sent for parsing will always have a value. If not they bomb out. either way I am using the goodies in http://www.sommarskog.se/arrays-in-sql.htmlThanks !! |
|
|
|
|
|
|
WHERE clause question