Reporting Stored Procedure

[Guest]

I want to adapt a Stored Procedure like the following for reporting purposes.
select
frmInstId,
frmName,
frmDueDate,
country,
description,
frmLastStatus
flgStat1,
flgStat2,
flgStat3,
flgStat4,
flgStat5
FROM
tblFrmDef,
tblFrmInst,
tblFrmHist,
tblObjects
WHERE
tblFrmDef.frmDefId = tblFrmInst.frmDefId and
tblFrmInst.objId = tblObjects.objId and
tblFrmInst.frmInstId = tblFrmHist.frmInstId and
tblFrmInst.flgActive=1


The problem is that the report can potentially take numerous permutations of
numerous parameters, including:
-tblFrmInst.frmDueDate (range possible)
-tblObjects.user
-tblFrmInst.flgActive (1 or 0)
-tblDef.frmName,
-tblFrmHist.frmLastStatus,
-flgStat1,
-flgStat2,
-flgStat3,
-flgStat4
-flgStat5

Short of building a heavily nested if...then...else statement to build the
correct select statement, are there any better ideas?

 

[W.G. Ryan MVP]

Patrick:

If all of those values at the bottom are paramaters, you can do this:

Where...

FieldName = @ParameterName OR @ParameterName = Null

Doing that for each of the values in question.

If I'm misunderstanding you though and you need different fields to show up,
then you will need to nest the ifs in all likelihood or, depending on the
reporting tool you're using, hide the fields based on whatever logic you are
employing to make these determinations.

 

[Guest]

Apologies, posted in error! intended for the sqlserver.server newsgroup!