Dynamic SQL Query Building

Create SQL Table


CREATE TABLE [dbo].[Employee](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Pin] [int] NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Insert Data in to Employee Table

INSERT INTO Employee([Name],[Address],[Phone],[Pin])VALUES('Sabu','Cochin','9876543210',682030)
INSERT INTO Employee([Name],[Address],[Phone],[Pin])VALUES('Arun','TVM','9876543210',789456)
INSERT INTO Employee([Name],[Address],[Phone],[Pin])VALUES('Rahul','BGLR','1472583690',456782)
INSERT INTO Employee([Name],[Address],[Phone],[Pin])VALUES('Ann','DELHI','1234566985',256314)
INSERT INTO Employee([Name],[Address],[Phone],[Pin])VALUES('Vipin','Cochin','5852369641',682020)

Procedure for searching for Employees
---------------------------------------------------


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE pr_SearchEmployee
-- Add the parameters for the stored procedure here
@EmployeeName varchar(20)
,@EmployeeAddress nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @searchQuery varchar(300)
SET @searchQuery = 'SELECT [ID]
,[Name]
,[Address]
,[Phone]
,[Pin]
FROM Employee WHERE '
IF len(@EmployeeName) > 0
BEGIN
SET @searchQuery = @searchQuery + '[Name] LIKE ' + char(39) + '%' + @EmployeeName + '%' + char(39)
END
ELSE IF len(@EmployeeAddress) > 0
BEGIN
SET @searchQuery = @searchQuery + '[Address] LIKE ' + char(39) + '%' + @EmployeeAddress + '%' + char(39)
END
EXEC (@searchQuery)
END
---------------------------------------------------------------------------
Execute Procedure
EXEC pr_SearchEmployee '','Cochin'