Creating Simple Search Stored Procedure:
Create the Table Candidate:

CREATE TABLE dbo.Candidate
(
CandidateID INT IDENTITY(1, 1)
PRIMARY KEY CLUSTERED,
FirstName NVARCHAR(255) DEFAULT NULL,
LastName NVARCHAR(255) DEFAULT NULL,
Address1 NVARCHAR(255) DEFAULT NULL,
Address2 NVARCHAR(255) DEFAULT NULL,
Address3 NVARCHAR(255) DEFAULT NULL,
City NVARCHAR(255) DEFAULT NULL,
ZIP NVARCHAR(255) DEFAULT NULL
)
GO

Search Procedure:
This search procedure,we can input any combination of input parameters. Based on input parameter ,I construct the dynamic SQL and use SP_Executesql to execute the dynamic string.



IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID('SP_GetCandidateDetails')
AND type = 'P' )
BEGIN
DROP PROCEDURE dbo.SP_GetCandidateDetails
END
go

CREATE PROCEDURE [dbo].[SP_GetCandidateDetails]

(
@FirstName NVARCHAR(255) = NULL,
@LastName NVARCHAR(255) = NULL,
@Address1 NVARCHAR(255) = NULL,
@Address2 NVARCHAR(255) = NULL,
@Address3 NVARCHAR(255) = NULL,
@City NVARCHAR(255) = NULL,
@ZIP NVARCHAR(255) = NULL
)
AS
BEGIN
SET nocount ON
-- DECLARE @FirstName NVARCHAR(255),
-- @LastName NVARCHAR(255),
-- @Address1 NVARCHAR(255),
-- @Address2 NVARCHAR(255),
-- @Address3 NVARCHAR(255),
-- @City NVARCHAR(255),
-- @ZIP NVARCHAR(255)
--SANITIZE THE USER INPUT
SET @FirstName = dbo.fn_CheckSQLInjection(@FirstName)--='FirstName'
SET @LastName = dbo.fn_CheckSQLInjection(@LastName)--'LastName'
SET @Address1 = dbo.fn_CheckSQLInjection(@Address1)--'Address1'
SET @Address2 = dbo.fn_CheckSQLInjection(@Address2)--'Address2'
SET @Address3 = dbo.fn_CheckSQLInjection(@Address3)--''
SET @City = dbo.fn_CheckSQLInjection(@City)--'City'
SET @ZIP = dbo.fn_CheckSQLInjection(@ZIP)--'Zip'
DECLARE @dsql NVARCHAR(4000)
--set @dsql='select * from Candidate'
--DECLARE @dsql NVARCHAR(4000)
IF ( ( @FirstName IS NULL )
OR ( LEN(@FirstName) = 0 )
)
BEGIN
SET @dsql = NULL
END
ELSE
BEGIN
IF ( CHARINDEX('*', @FirstName) > 0 )
BEGIN
SET @FirstName = REPLACE(@FirstName, '*', '%')
SET @dsql = 'SELECT * from Candidate where FirstName LIKE ''' + @FirstName + ''''
END
ELSE
BEGIN
SET @dsql = 'SELECT * from Candidate where FirstName = ''' + @FirstName + ''''
END
END
-- SELECT @dsql
IF ( ( @LastName IS NULL )
OR ( LEN(@LastName) = 0 )
)
BEGIN
SET @dsql = @dsql
END
ELSE
BEGIN
IF ( CHARINDEX('*', @LastName) ) > 0
BEGIN
SET @LastName = REPLACE(@LastName, '*', '%')
SET @dsql = ISNULL(@dsql + ' and LastName LIKE ''' + @LastName + '''',
'SELECT * from Candidate where LastName LIKE ''' + @LastName + '''')
END
ELSE
BEGIN
SET @dsql = ISNULL(@dsql + ' and LastName = ''' + @LastName + ''' ',
'SELECT * from Candidate where LastName = ''' + @LastName + '''')
END
END
--SELECT @dsql
IF ( ( @Address1 IS NULL )
OR ( LEN(@Address1) = 0 )
)
BEGIN
SET @dsql = @dsql
END
ELSE
BEGIN
IF ( CHARINDEX('*', @Address1) ) > 0
BEGIN
SET @Address1 = REPLACE(@Address1, '*', '%')
SET @dsql = ISNULL(@dsql + ' and Address1 LIKE ''' + @Address1 + '''',
'SELECT * from Candidate where Address1 LIKE ''' + @Address1 + '''')
END
ELSE
BEGIN
SET @dsql = ISNULL(@dsql + ' and Address1 = ''' + @Address1 + ''' ',
'SELECT * from Candidate where Address1 = ''' + @Address1 + '''')
END
END
--SELECT @dsql

IF ( ( @Address2 IS NULL )
OR ( LEN(@Address2) = 0 )
)
BEGIN
SET @dsql = @dsql
END
ELSE
BEGIN
IF ( CHARINDEX('*', @Address2) ) > 0
BEGIN
SET @Address2 = REPLACE(@Address2, '*', '%')
SET @dsql = ISNULL(@dsql + ' and Address2 LIKE ''' + @Address2 + '''',
'SELECT * from Candidate where Address2 LIKE ''' + @Address2 + '''')
END
ELSE
BEGIN
SET @dsql = ISNULL(@dsql + ' and Address2 = ''' + @Address2 + ''' ',
'SELECT * from Candidate where Address2 = ''' + @Address2 + '''')
END
END
--SELECT @dsql

IF ( ( @Address3 IS NULL )
OR ( LEN(@Address3) = 0 )
)
BEGIN
SET @dsql = @dsql
END
ELSE
BEGIN
IF ( CHARINDEX('*', @Address3) ) > 0
BEGIN
SET @Address3 = REPLACE(@Address3, '*', '%')
SET @dsql = ISNULL(@dsql + ' and Address3 LIKE ''' + @Address3 + '''',
'SELECT * from Candidate where Address3 LIKE ''' + @Address3 + '''')
END
ELSE
BEGIN
SET @dsql = ISNULL(@dsql + ' and Address3 = ''' + @Address3 + ''' ',
'SELECT * from Candidate where Address3 = ''' + @Address3 + '''')
END
END
-- SELECT @dsql
IF ( ( @City IS NULL )
OR ( LEN(@City) = 0 )
)
BEGIN
SET @dsql = @dsql
END
ELSE
BEGIN
IF ( CHARINDEX('*', @City) ) > 0
BEGIN
SET @City = REPLACE(@City, '*', '%')
SET @dsql = ISNULL(@dsql + ' and City LIKE ''' + @City + '''',
'SELECT * from Candidate where City LIKE ''' + @City + '''')
END
ELSE
BEGIN
SET @dsql = ISNULL(@dsql + ' and City = ''' + @City + ''' ',
'SELECT * from Candidate where City = ''' + @City + '''')
END
END
--SELECT @dsql
IF ( ( @ZIP IS NULL )
OR ( LEN(@ZIP) = 0 )
)
BEGIN
SET @dsql = @dsql
END
ELSE
BEGIN
IF ( CHARINDEX('*', @ZIP) ) > 0
BEGIN
SET @ZIP = REPLACE(@ZIP, '*', '%')
SET @dsql = ISNULL(@dsql + ' and ZIP LIKE ''' + @ZIP + '''',
'SELECT * from Candidate where ZIP LIKE ''' + @ZIP + '''')
END
ELSE
BEGIN
SET @dsql = ISNULL(@dsql + ' and ZIP = ''' + @ZIP + ''' ',
'SELECT * from Candidate where ZIP = ''' + @ZIP + '''')
END
END
EXECUTE SP_EXECUTESQL @dsql
SELECT @dsql
END

FUNCTION USED TO HANDLE SINGLE QUOTES:

CREATE FUNCTION [dbo].[fn_CheckSQLInjection] ( @string NVARCHAR(255) )
RETURNS NVARCHAR(255)
AS BEGIN
IF ( ( @string IS NULL )
OR ( LEN(@string) ) = 0
)
RETURN @string
ELSE
SELECT @string = REPLACE(@string,'''','''''' )
RETURN @string
END