Filtering with SQL Server Stored Procedure

Filtering and Searching are to different scenarios. In searching query search according to your criteria and returns any results found filtering does the same thing but it ignores the things as your query specifies. The example i'm going to explain is related to filtering through a single SQL Server table. Lets say we have the below table in our database.

SET QUOTED_IDENTIFIER ON

GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCustomer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](500) NULL,
[NIC] [varchar](50) NULL,
[Address] [varchar](500) NULL,
[FromDate] [date] NULL,
[ToDate] [date] NULL,
CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

We need to pass parameters to our SQL query for filtering. Stored procedure accepts parameters for all the fields in the table.

CREATE PROCEDURE spFilter

@Name VARCHAR(500)=NULL,
@Address VARCHAR(500)=NULL,
@NIC VARCHAR(12)=NULL,
@FromDate DATE=NULL,
@Todate DATE
AS
BEGIN

DECLARE @Statement VARCHAR(MAX)

SET @Statement='SELECT [Name],[NIC],[Address],[FromDate],[ToDate] FROM tblCustomer WHERE 1=1 '

IF @Name IS NOT NULL AND LEN(@Name)>0
BEGIN

SET @Statement=@Statement+' AND Name LIKE ''%'+@Name+'%'' '

END

IF @Address IS NOT NULL AND LEN(@Address)>0
BEGIN

SET @Statement=@Statement+' AND Address LIKE ''%'+@Address+'%'' '

END

IF @NIC IS NOT NULL AND LEN(@NIC)>0
BEGIN

SET @Statement=@Statement+' AND NIC = '''+@NIC+''' '

END

IF @FromDate IS NOT NULL AND LEN(@FromDate)>0 AND @Todate IS NOT NULL AND LEN(@Todate)>0
BEGIN

 SET @Statement=@Statement+ ' AND FromDate between CONVERT(date,'''+CONVERT(VARCHAR,@FromDate)+''',103) AND CONVERT(date,'''+CONVERT(VARCHAR,@Todate)+''',103)'

END

END

In stored procedure it checks whether any passed parameter is null or empty if it's not it adds the search criteria to the where clause of the query if the parameter is null or empty it ignore the parameter from the search query.