SPROC Problem

  • 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

  • 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)

     

  • 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

  • thanks for the replies .My problem is now solved

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply