August 24, 2006 at 8:37 am
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
August 24, 2006 at 9:05 am
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)
August 25, 2006 at 11:07 am
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
August 25, 2006 at 1:49 pm
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