Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 13th, 2004, 07:49 PM
KYC KYC is offline
Registered User
 
Join Date: Feb 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Optional WHERE clause in sproc

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!
 
Old February 14th, 2004, 09:49 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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:
Code:
... WHERE source_cd = COALESCE(@parameter,source_cd) ...
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
 
Old February 17th, 2004, 03:40 PM
KYC KYC is offline
Registered User
 
Join Date: Feb 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!




Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with sproc Bob Bedell SQL Server 2005 10 January 20th, 2008 08:52 AM
dynamic sproc sabry SQL Language 1 August 25th, 2006 06:23 AM
When a sproc or table was Last Used dlandolin SQL Server 2000 1 September 29th, 2005 08:09 PM
Working with Dates in SPROC hugh@kmcnetwork.com SQL Language 4 March 10th, 2004 03:56 PM
WHERE BETWEEN search condition in sproc jtyson SQL Server 2000 1 August 7th, 2003 09:22 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.