Problem
I would like to be able to use one stored procedure to query my SQL Server database using one or more prameters. I have a search form on my ASP.NET web site that allows the user to search using multiple fields in my SQL Server database. If the user leaves the form blank and clicks submit, I would like the SQL stored procedure to return all of the records.
Solution
Here is a template for creating a SQL stored procedure that accepts 0 to n number of parameters. If no stored procedure prameters are specified when executing the stored procedure, it will return all records in the SQL database table.
/*
This is a dynamic search proc. It uses COALESCE and ISNULL to determine how to
search for a record in the a table.
*/
CREATE PROCEDURE [dbo].USP_User_Search
@UserID int = NULL,
@FirstName varchar(50) = NULL,
@LastName varchar(50) = NULL,
@DateCreatedBegin datetime = NULL,
@DateCreatedEnd datetime = NULL
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
[ID],
[FirstName],
[LastName]
[DateCreated]
FROM
[dbo].[User]
WHERE
[UserID] = COALESCE(@UserID, UserID) AND
-- Example using SOUNDEX to get records where the user has a last name that sounds
-- similar to what was passed in as a stored procedure parameter
SOUNDEX(LastName) = COALESCE(SOUNDEX(@LastName), SOUNDEX(LastName)) AND
-- Example of using LIKE with an ISNULL instead of COALESCE. If you pass in 'mic'
-- as the value for the stored procedure's @FirstName parameter, you would match
-- Michelle, Michael, etc...
FirstName LIKE ISNULL('%' + @FirstName + '%', '%') AND
-- This shows how to handle dates using COALESCE and ISNULL to search a date range.
-- The time concatonation is important. Otherwise you may not get all the records back.
ISNULL(DateCreated, '') >= COALESCE(@DateCreatedBegin + '00:00:00', ISNULL(DateCreated, '')) AND
ISNULL(DateCreated, '') <= COALESCE(@DateCreatedEnd + '23:59:59', ISNULL(DateCreated, ''))
ORDER BY a.DateCreated
GO