I have two stored procedures that are identical except for one condition in the WHERE clause.

When the data returned is for a specific department, a specific condition in the WHERE clause is needed. For example, source_cd IN ('01','02')

When the data returned is for the entire organization, that condition can not be used at all [the field is populated for all records; null is not permitted]. And while there is a short list of correct values for that field, I don't want to exclude any records that happen to have an incorrect value there.

My sproc input parameters filter for different departments, but not for departments vs the entire organization. Do I have to maintain the two separate sprocs or is there any way around this?

In advance, thank you very much!

I think the 'IN' operator complicates things a bit. If we ignore that for the moment, the usual 'trick' for this sort of thing is to define a parameter which if it has a value, then the query is to use that value in a WHERE clause; if it doesn't, i.e. the parameter value is NULL, then the parameter means "don't care" - you'll take any value in that column. So in your example a NULL value would mean "all departments"

You use the COALESCE function, as:
If the parameter is not NULL, then 'source_cd' is compared to the parameter's value. If the parameter is NULL, then 'source_cd' is compared to itself, which is always TRUE, which results in all rows being selected.

I'm not sure how you are handling the IN clause via parameters...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Excellent. I’m still testing the results, but this appears to work.

I changed the filter to

WHERE source_cd IN
        (
            (COALESCE(@mysource1,source_cd)),
            @mysource2,
            @mysource3
        )

I use an input parameter to convert department name acronym into the proper field codes.

So, an input parameter of, say, ‘sos’, results in @mysource1, @mysource2 and @mysource3 being set to the proper codes for that department.

@mysource2 and @mysource3 variables are optional.

An input parameter of ‘inc’, meaning the entire company, results in @mysource1, @mysource2 and @mysource3 all being null.

Thanks so much!