Handling optional parameters

On many reports I have optional parameters, for example if the employee is filled in it will run for that employee but if it's null it runs for all employees. Which is the best way to handle this?

The two options I am currently looking at are:

select * from employee where (employee.id = @.EmpID or @.EmpID is Null)

select * from employee where isnull(@.empID, employee.id) = employee.id

Anyone else have a different solution?

Currently we use the OR approach and I wanted to see if anyone had any thoughts before switching to using IsNull, which seems a bit better.

OR can be slow. I always use the ISNULL approach.

|||

Another way to do this is, you can include "*(ALL) " in you employee dataset and you can default the parameter to "*(ALL)". Now pass back the selected value to the Stored Procedure

IF (@.EMPID= '*(ALL)'

select * from Employee

Else

Select * form Employee where employee.id=@.EMPID

This way it is more intuitive to End Users.

|||

The problem with using the if (@.EmpID is null) is that then you need to maintain two queries. And for some reports that have 3 or more optional parameters this could mean having 8 or more queries to maintain just for optional parameters. We also thought about using an expression for the query but some of the clients like to use the generic query view if they edit the report so this makes that option unavailable.

I think we will be using the isnull option for our solution.

Thanks

|||

I thought this blog post was useful for this topic. You can combine approaches if you want.

http://bloggingabout.net/blogs/egiardina/archive/2007/06/26/sql-server-reporting-services-optional-parameters.aspx

|||

Techquest wrote:

Another way to do this is, you can include "*(ALL) " in you employee dataset and you can default the parameter to "*(ALL)". Now pass back the selected value to the Stored Procedure

IF (@.EMPID= '*(ALL)'

select * from Employee

Else

Select * form Employee where employee.id=@.EMPID

This way it is more intuitive to End Users.

Not everyone uses stored procs to serve reports.

|||

Found an issue using the IsNull approach. If the optional parameter can be null in the table it will not return the null values.

EX. Middle name is a nullable field

select * from Employee where MiddleName = IsNull(@.MiddleName, MiddleName)

This won't return all values when the parameter is null, will return all values that have any non null MiddleName.

Think it may be safer to use the OR approach now because other people that use modify reports not familiar with this may use IsNull is cases it should not be used.

Anyone have any suggestions around this? I know most of the time the optional parameter does not go against a nullable field but could cause a lot of problems if you did it by mistake.

Thanks,

Kevin

|||

Yeah, avoid nullable fields

In case you do have them though you could use:

select * from Employee where COALESCE(MiddleName, '') = COALESCE(@.MiddleName, MiddleName, '')

COALESCE is basibcally like ISNULL but it takes any number of parameters rather than just 2 and returns the first non-null value.

|||

COALESCE functions differently than IsNull though. If you look at the execution plan, these both are the same:

select * from employee where employee.id = COALESCE(@.EmpID, employee.id)

select * from employee where (employee.id = @.EmpID or @.EmpID is Null)

This defeats the purpose of using IsNull for the better exectution with some indexes.