Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 16th, 2004, 04:29 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default Ignoring nulls in stored proc

I need to write a stored procedure in Sybase that will return a simple result set from one table based on a few criteria passed as parameters to the procedure. Basically the sp has a parameter for each column in the table and I want to return all rows that meet the specified criteria, the catch is that I wan to be able to ignore null parameters so that criterion is not used in the query.

For example:

I have a table called Cars w/ the following columns:

make,
model
size
color

assume the following data:

make model size color

VW Jetta sedan blue
VW Jetta sedan red
VW GTI hatchback blue
VW GTI hatchback green
BMW 3.25 coupe red
BMW 3.25 sedan red
BMW 5.40 sedan black

now I need to write a stored proc that takes a parameter for each column and will return the rows that have matching values for the corresponding columns.

Thus, if the sp signature was my_proc(@make, @model, @size, @color)

I want the following behavior:

my_proc('VW', null, null, 'blue') returns
  VW, Jetta, sedan, blue
  VW, GTI, hatchback, blue

my_proc('BMW', '5.40', 'sedan', 'black') returns
  BMW, 5.40, sedan, black

my_proc(null, null, 'sedan', 'green') returns
  empty set

I've tried using case statements but I don't think that is the correct way to approach this. If anyone knows of an elegant solution I would much appreciate any help.

Thanks
Reply With Quote
  #2 (permalink)  
Old June 16th, 2004, 05:56 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi 12th_Man,

You may go about using this logic.

Code:
Create Procedure my_proc(
--You can decide on the datatype and length of these parameters
@make char(10)=NULL, 
@model char(10)=NULL, 
@size char(10)=NULL, 
@color char(10)=NULL)
as

    Declare @strSql char(255)
    Set @strSql="Select * from Cars"
    If @make is NOT NULL and @model is NOT NULL and @size is NOT NULL and @color is NOT NULL
        Set @strSQL=@strSql + ' where'
    ELSE
        BEGIN
        Select "No Criteria define for selection. Parameter expected."
        RETURN
        END

    If @make is NOT NULL
        Set @strSql=@strSql + ' make = ' + @make

    If @make is NULL and @model is NOT NULL
        Set @strSql=@strSql + ' model = ' + @model
    If @make is NOT NULL and @model is NOT NULL
        Set @strSql=@strSql + ' and  model = ' + @model

    If @make is NULL and @model is NULL 
        IF @size is NOT NULL
            Set @strSql=@strSql + ' size = ' + @size
    ELSE
        IF @size is NOT NULL
            Set @strSql=@strSql + ' and  size = ' + @size

    If @make is NULL and @model is NULL and @size is NULL
        IF @color is NOT NULL
            Set @strSql=@strSql + ' color = ' + @color
    ELSE
        IF @size is NOT NULL
            Set @strSql=@strSql + ' and  color = ' + @color

Exec(strSql)
I got to believe myself, so I haven't tested this out;)
May be someone would post an easier way to achieve this.

Hope that helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection
Reply With Quote
  #3 (permalink)  
Old June 16th, 2004, 08:03 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Does Sybase implement the COALESCE function?

If so, then you can use it in a little trick to handle a "don't care" condition. That is, you want to select a row if the column being tested is equal to a parameter, but if the parameter is NULL, then you don't care what the value is for the column.

The WHERE clause would thus be:

    WHERE yourcolumn=COALESCE(@parameter, yourcolumn) ...

If @parameter is not NULL then the test is the column value compared to the parameter value, but if the parameter is NULL the COALESCE returns the column value itself instead of the parameter value, which results in the column being compared to itself, which is always true, which means the test is in effect a no-op, or "don't care" what the value of the column is.

Repeat as necessary by ANDing the WHERE expressions for the other columns and their respective parameters...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #4 (permalink)  
Old June 17th, 2004, 07:49 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Jeff, that's an excellent solution, just what I'm looking for.
Reply With Quote
  #5 (permalink)  
Old June 17th, 2004, 02:11 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Good one Jeff. That didn't strike my head then.

Cheers!

_________________________
-Vijay G
Strive for Perfection
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Stored Proc vs Trigger carumuga SQL Server 2005 1 January 7th, 2008 11:38 AM
sending NULLs into a SQL Server PROC lisabb ASP.NET 2.0 Basics 1 June 21st, 2007 03:36 PM
Calling an insert stored proc from a select stored dzitam SQL Language 10 April 2nd, 2007 12:39 PM
How to get value from stored proc busybee ASP.NET 1.0 and 1.1 Basics 4 April 2nd, 2006 01:06 AM
Very Complicated Stored Proc monfu SQL Server 2000 7 November 29th, 2005 09:02 PM



All times are GMT -4. The time now is 08:26 PM.


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