Re: IIF in where clause to exclude selection criteri when null
From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 06/23/04
- Previous message: baz: "IIF in where clause to exclude selection criteri when null"
- In reply to: baz: "IIF in where clause to exclude selection criteri when null"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 23 Jun 2004 19:42:45 +0530
hi baz,
if i understand you correctly you are looking for query as follows, what
this query will do is check the value of @acctnum variable, if it is 'all'
then no condition for acctnum will be checked otherwise acctnum will be
filtered out for the equivalent value of @acctnum variable
Select * from a
WHERE AcctNum = case when @AcctNum ='all' then AcctNum else @acctnum end
and a.FISCYR + a.FISCPER <= @year + @period
see similar following example on northwind database.
--if @acctnum = 'all' retrieve all the rows from table customers
declare @AcctNum varchar(50)
set @AcctNum ='all'
Select * from customers
WHERE customerid = case when @AcctNum ='all' then customerid else @acctnum
end
--change value of @acctnum is specific customerid,
-- and same query will retrieve releted rows from table customers
set @AcctNum ='alfki'
Select * from customers
WHERE customerid = case when @AcctNum ='all' then customerid else @acctnum
end
-- Vishal Parkar vgparkar@yahoo.co.in | vgparkar@hotmail.com
- Previous message: baz: "IIF in where clause to exclude selection criteri when null"
- In reply to: baz: "IIF in where clause to exclude selection criteri when null"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
