**case cluase**

Hi
I'm working with SQL2000, and I want to have a following result I tried it
with case clause but I couldn't get the desired result.
Table1(code1,date1)
I want to check the variable @.status if it's 0 then I want to fetch all
rows without any condition but if it's 1 then I want the range of rows
which their date1 column are between @.mindate and @.maxdate,how can I have
this structure in one select statement?
I would be grateful if anybody can help me.try this:
SELECT
code1,
date1
FROM
Table1
WHERE
@.status IS NULL
OR (@.Status IS NOT NULL AND date1 > @.mindate AND date1 < @.maxdate)
- Baileys
R-M wrote:
> Hi
> I'm working with SQL2000, and I want to have a following result I tried
> it with case clause but I couldn't get the desired result.
> Table1(code1,date1)
> I want to check the variable @.status if it's 0 then I want to fetch all
> rows without any condition but if it's 1 then I want the range of rows
> which their date1 column are between @.mindate and @.maxdate,how can I
> have this structure in one select statement?
> I would be grateful if anybody can help me.|||Hi
If @.status =0
SELECT here
else
Another SELECT here
<R> wrote in message news:ops9qo9lxymw7tkz@.system109.parskhazar.net...
> Hi
> I'm working with SQL2000, and I want to have a following result I tried it
> with case clause but I couldn't get the desired result.
> Table1(code1,date1)
> I want to check the variable @.status if it's 0 then I want to fetch all
> rows without any condition but if it's 1 then I want the range of rows
> which their date1 column are between @.mindate and @.maxdate,how can I have
> this structure in one select statement?
> I would be grateful if anybody can help me.|||Untested but try this
select code1,date1
from Table1
where @.status=0
or (@.status=1 and date1 between @.mindate and @.maxdate)|||Try,
-- if passing time part with @.mindate and @.maxdate
select *
from dbo.t1
where
date1 between case when @.status = 0 then '17530101' else @.mindate end
and case when @.status = 0 then '9999-12-31T23:59:59.997' else @.maxdate end
-- comparing just date part
select *
from dbo.t1
where
date1 >= case when @.status = 0 then '17530101' else convert(char(8),
@.mindate, 112) end
and date1 < case when @.status = 0 then '9999-12-31T23:59:59.997' else
dateadd(day, 1, convert(char(8), @.maxdate, 112)) end
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"R-M" wrote:

> Hi
> I'm working with SQL2000, and I want to have a following result I tried it
> with case clause but I couldn't get the desired result.
> Table1(code1,date1)
> I want to check the variable @.status if it's 0 then I want to fetch all
> rows without any condition but if it's 1 then I want the range of rows
> which their date1 column are between @.mindate and @.maxdate,how can I have
> this structure in one select statement?
> I would be grateful if anybody can help me.
>