Dynamic SQLÂ Search Stored Procedure
Create Candidate table and populate sample data to demonstrate simple search through Dynamic SQL.
This includes how to safeguard against SQL Injection by escaping single quotes through SQL function.
Create SimpleSearch Database , Candidate Table and Populate Sample Data
CREATE DATABASE SimpleSearch GO USE SimpleSearch GO 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 INSERT INTO dbo.Candidate VALUES ( N'Stefanie', N'Buckley', N'36 West Fabien St.', N'89 Green Nobel Freeway', N'426 Fabien Blvd.', N'Montgomery', N'86036' ), ( N'Sandy', N'Mc Gee', N'935 Nobel Way', N'54 South Second Parkway', N'61 Green Nobel Road', N'St. Paul', N'98849' ), ( N'Lee', N'Warren', N'91 Hague Parkway', N'85 Old Avenue', N'298 South Nobel Boulevard', N'Bakersfield', N'18483' ), ( N'Regina', N'Forbes', N'50 Cowley Avenue', N'860 East Rocky Fabien Freeway', N'81 South Milton Street', N'Yonkers', N'07514' ), ( N'Daniel', N'Kim', N'525 North Old Parkway', N'36 Green Nobel Blvd.', N'171 Fabien Drive', N'Glendale', N'09047' ), ( N'Dennis', N'Nunez', N'48 Old Freeway', N'412 Second Drive', N'271 First Street', N'Des Moines', N'21100' ), ( N'Myra', N'Zuniga', N'545 New Way', N'13 New Boulevard', N'15 Milton St.', N'Anaheim', N'15986' ), ( N'Teddy', N'Ingram', N'80 White New St.', N'559 Rocky Fabien Avenue', N'514 Clarendon Freeway', N'Cincinnati', N'05975' ), ( N'Annie', N'Larson', N'16 Nobel St.', N'96 White Nobel Drive', N'400 South Green Milton Freeway', N'Aurora', N'12879' ), ( N'Herman', N'Anderson', N'361 Milton Way', N'72 White Second Road', N'598 North Second Way', N'Omaha', N'86163' )
Function to Safeguard against SQLInjection
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
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_excutesql 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;
Sample Stored Procedure Execution
USE [SimpleSearch] GO DECLARE @RC INT DECLARE @FirstName NVARCHAR(255) DECLARE @LastName NVARCHAR(255) DECLARE @Address1 NVARCHAR(255) DECLARE @Address2 NVARCHAR(255) DECLARE @Address3 NVARCHAR(255) DECLARE @City NVARCHAR(255) DECLARE @ZIP NVARCHAR(255) -- TODO: Set parameter values here. EXECUTE @RC = [dbo].[SP_GetCandidateDetails] @FirstName = 'Teddy' ,@LastName = NULL ,@Address1 = NULL ,@Address2 = NULL ,@Address3 = NULL ,@City = NULL ,@ZIP = NULL GO