A search or find using optional parameters

				
--**************************************
-- Name: A search or find using optional parameters
-- Description:This stored procedure demonstrates how to create a search that accepts multiple optional parameters. Only parameters that are provided with values will be used to filter the results. This is useful for dynamic searches when the user selects or provides values for one or more fields to be searched, but not all of the fields are required.
-- By: Andrew Corley
--
-- Inputs:Using the standard pubs..authors SQL server table, accepts a last name, first name, zip code, and contract flag as varchar values.
--
-- Returns:A result set filtered only by the parameters that were given values.
--
-- Assumes:This should work in SQL 6.5, but I could only test this using SQL Server 2000 with the database set to 65 compatibility level. This code was written explicitly to avoid using the SQL EXEC string function for several reasons. 1) Do not assume that this query method hampers performance. In large tables with proper indexes, this query performs well. 2) By using a string exec, the SQL code in the exec statement is not precompiled and optimized like a stored procedure. 3) The EXEC statement leaves an application open to SQL Injection attacks, something that hackers can easily exploit. <http://www.sitepoint.com/article/794> 4) Users should not be granted direct access to tables, which is required with the EXEC string statement. 5) All access to tables should be via stored procedures, which the users execute. As you can see although the string EXEC function is very powerful, it is does not fit within enterprise and web applications due to security, reliability and performance issues.
--
-- Side Effects:Only a large table, the like clause may cause performance issues. Also, converting numeric, bit, and datetime fields to char(n) for the LIKE test in the where clause may cause performance issues. Additional indexes and/or computed fields may resolve these issues.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=737&lngWId=5--for details.--**************************************

CREATE PROCEDURE usp_FindAuthor
	
	/* All parameters are optional */
	@LastName varchar(200) = NULL,
	@FirstName varchar(200) = NULL,
	@ZipCode varchar(200) = NULL,
	@Contract varchar(200) = NULL
AS
IF @LastName IS NULL
BEGIN
	/* If the parameter is null, then this statement will
		cause the select to not filter by last name.	 */
	SET @LastName = '%'
END
ELSE
BEGIN
	/* Since we want to search for this last name, add the
		wildcard to require the select to return all
		last names that begin with this value.		*/
	SET @LastName = @LastName + '%'
END
IF @FirstName IS NULL
BEGIN
	/* If the parameter is null, then this statement will
		cause the select to not filter by first name.	 */
	SET @FirstName = '%'
END
ELSE
BEGIN
	/* Since we want to search for this last name, add the
		wildcard to require the select to return all
		first names that begin with this value.		*/
	SET @FirstName = @FirstName + '%'
END
IF @ZipCode IS NULL
BEGIN
	/* If the parameter is null, then this statement will
		cause the select to not filter by zip code.	 */
	SET @ZipCode = '%'
END
ELSE
BEGIN
	/* Since we want to search for this zip code, add the
		wildcard to require the select to return all
		zip codes that begin with this value.		*/
	SET @ZipCode = @ZipCode + '%'
END
IF @Contract IS NULL
BEGIN
	/* If the parameter is null, then this statement will
		cause the select to not filter by contract.	 */
	SET @Contract = '%'
END
	/* Since we want to search for contracted authors and
		this is a bit field, do not add the wildcard.
		The same is true for numeric fields and dates. 	*/
SELECT 
	au_id, 
	au_lname, 
	au_fname, 
	phone, 
	address, 
	city, 
	state, 
	zip, 
	contract 
FROM 
	pubs..authors	WITH (NOLOCK)
WHERE
	au_lname LIKE @LastName
	AND
	au_fname LIKE @FirstName
	AND
	zip LIKE @ZipCode
	AND
	contract LIKE @Contract