SPROC Problem

• Christopher Mark

  SSC-Addicted

  Points: 417

  More actions

  August 24, 2006 at 8:37 am

  #173915

  I have a sproc which has 5 input parameters each with a default value of null. Within the sproc i want to check if the params =null if they dont then they should be included in the WHERE clause of  a select statement. At the moment im having to use multiple if statements to accomplish this, is there any alternative methods.

  Any help much appreciated

  thanks

• ramses2nd

  Hall of Fame

  Points: 3720

  More actions

  August 24, 2006 at 9:05 am

  #656612

  Try using the COALESCE function.  What it does is tests for the first non null value.  You can check for details in BOL but here's and example:

  SELECT EMP_NUM, EMP_DESCRIPTION

  FROM EMP

  WHERE EMP_DESCRIPTION LIKE COALESCE(@desc,EMP_DESCRIPTION)

   

• vadba

  SSChampion

  Points: 11133

  More actions

  August 25, 2006 at 11:07 am

  #656883

  If you mean that the five parameters are compare to an associated column in a table, there are a couple of things you could do, depending on your situation.  For character data, I sometimes convert NULL to '%' and use the LIKE operator instead of the = operator.

  In general, try using the format shown in this example:

  CREATE PROC uspTest

  (

    @ev_year int

  , @ev_state varchar(2)

  )

  AS

  SELECT ev_date, ev_state, ev_city

    FROM events

   WHERE ev_year = CASE

                     WHEN @ev_year IS NULL

                       THEN ev_year

                       ELSE @ev_year

                   END

   AND ev_state = CASE

                      WHEN @ev_state IS NULL

                        THEN ev_state

                        ELSE @ev_state

                    END

• Christopher Mark

  SSC-Addicted

  Points: 417

  More actions

  August 25, 2006 at 1:49 pm

  #656923

  thanks for the replies .My problem is now solved