July 10, 2008 at 2:12 am
Hi
I need to create SP for searching in a table. I have 4 input parameters in that 3 are optional and can be given in any combination. matching records should be returned from a table for the given inputs
Example
Table:Employee
Columns :
EmpID
EmpName
Salary
Age
City
Country
InputParameters
Salary
Age
City
Country - Mandatory requirement
If i enter country alone (leaving all other parameters null) SP should return all employees of that country.
If i enter country and city (leaving other 2 parameters null) SP should return all employees of that country and City.
and so on .... for other 2 parameters.
Please help how to construct the SP. Now I am trying with if .. else .. statement which needs lot of coding.
Muralidaran r
July 10, 2008 at 2:24 am
Hi,
I recommend using the COALESCE Function. It replaces a value with a new one if the value is null.
For example:
SELECT * FROM Employee WHERE Country = @Country AND Salary = COALESCE(@Salary, Salary) AND COALESCE(@City, City) AND COALESCE(@Age, Age)
So whenever one of your passed parameters are NULL, it will use the actual column value. Personally, this is one of my favourite and most useful functions!
qt
July 10, 2008 at 3:27 am
Thank you qtsohg.
Your suggestion works.
July 11, 2008 at 6:42 am
My boss does it this way
CREATE Procedure bla
@Country = NULL,
and so forth
AS
BEGIN
SELECT bla from bla
WHERE (Country = @Country) OR (@Country IS NULL)
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply