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 |
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
 Posted - 2004-09-13 : 01:17:43
|
| hi, i know i've read it somewhere here (can you please redirect me? tried searching but nothing came up) but can you help me with this one?declare @variable nvarchar(100)select...where fieldname like @variable --problem is here@variable maybe 'All','fieldname'. If the value of @variable is 'All' then i would search for all fieldnames if not then i'll use the specific variable value (no problem)thanks in advance... |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-09-13 : 01:34:15
|
| ? Are you looking for something like this?where (@variable = 'All' OR fieldname like '%' + @variable = '%')MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-13 : 01:37:42
|
| Thanks for the prompt reply Derrick,I found a partial answer:where fieldname =isnull(@variable,fieldname) but this doesn't cover the 'All' value. If it's 'All' then I expect everything will be returned since it doesn't matter which fieldname it is.found the answer... but if you can optimize (i read this will create table scans)where (fieldname =isnull(@variable,fieldname)) or (fieldname=replace(@variable,'All',fieldname)) |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-09-13 : 10:26:13
|
| This piece of code does the same thing as well:WHERE fieldname = ISNULL(NULLIF(@variable, 'All'), fieldname)OS |
|
|
|
|
|
|
null value in the where clause