Ignoring nulls in stored proc

12th_Man · June 16th, 2004, 04:29 PM

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

happygv · June 16th, 2004, 05:56 PM

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

Jeff Mason · June 16th, 2004, 08:03 PM

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

12th_Man · June 17th, 2004, 07:49 AM

Thanks Jeff, that's an excellent solution, just what I'm looking for.

happygv · June 17th, 2004, 02:11 PM

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

Cheers!

_________________________
-Vijay G

Strive for Perfection