sql server mseq: Hi all,

I've got a huge Reprot in Crystal 8.5.

I've discovered that I can't work with the report if I
change the Stored Proc the report is based on. (this is
a kown and popular issue )

SO what I want to do in save the report under a different
name as attempt a more latteral approach by changing the
Stored Proc instead.

The original report criteria limits on AcctNum

What I want to do is be able to exlude AcctNum from the
selection criteria if its "ALL"

Original stored proc:

Select * from a
WHERE
AcctNum = @AcctNum
AND
a.FISCYR + a.FISCPER <= @year + @period

--------

What I want to do is something like this:

Select * from a
WHERE
iif( @AcctNum = "ALL", , "AcctNum = " + @AcctNum + "
AND " )
a.FISCYR + a.FISCPER <= @year + @period

Any suggestions?

Thanks,
baz

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