January 12, 2006 at 5:30 pm
How do you write a Stored Procedure where the query must use only the parameters that were passed in?
For example, the user can search on ANY of these fields: FirstName, LastName, DateOfBirth, PhoneNumber
The user can pass in all 4 values which must be incorporated in the SELECT or only 1 value leaving the others blank.
I know that you can specify = DEFAULT in the parameter list, however this does not apply here. The question here is how do you write a query that searches only on the parameters that were specified and ignores the ones that were skipped by the user?
Thank you!!!
January 12, 2006 at 6:18 pm
You can use the CASE statement to test for parameters that were not passed in; if the test returns true then simply set the argument to itself, for example:
select *
from table_1 a
where a.firstname = CASE WHEN @firstname IS NULL THEN a.firstname else @firstname END
You can also use the ISNULL() function to perform the same thing, so in the last example, simple use:
where a.firstname = ISNULL(@firstname, a.firstname)
January 13, 2006 at 6:37 am
CREATE PROCEDURE test
@LastName VARCHAR(20) NULL,
@FirstName VARCHAR(20) NULL,
@DOB DATETIME NULL,
@Phone VARCHAR(10) NULL
AS
SELECT *
FROM Customer
WHERE LastName = ISNULL(@LastName, LastName)
AND FirstName = ISNULL(@FirstName, FirstName)
AND DOB = ISNULL(@DOB, DOB)
AND Phone = ISNULL(@Phone, Phone)
The only problem with this approach is if the value of any of the fields searched is NULL in the database the record is not going to be returned.
For example if you allow NULLs in Phone field the last line should be like this.
AND ISNULL(Phone,'') = ISNULL(@Phone, ISNULL(Phone,''))
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
January 13, 2006 at 6:39 am
PS.
I love the ISNULL function. In my opinion the coolest introduction to SQL Server.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply