how to perform sorting and filtering in stored procedure with performance optimization?

Question

I want to perform sorting and filtering in my stored procedure.

My create table for Holiday table:

CREATE TABLE [dbo].[Holiday](
    [HolidaysId] [int] IDENTITY(1,1) NOT NULL,
    [HolidayDate] [date] NULL,
    [HolidayDiscription] [nvarchar](500) NULL,
    [Name] [nvarchar](max) NULL,
 CONSTRAINT [PK_Holiday] PRIMARY KEY CLUSTERED 
(
    [HolidaysId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

My filtering criteria would be as:

1. Starts With
2. Is Equal to
3. Not Equal to.

Note:Please ignore HolidayId in filter comparision.

My Table:Holiday

HolidaysId int,Name nvarchar(500),HolidayDate date.

Sample Input:

HolidayId       Name       Date
 1               abc       1/1/2015
 2               pqr       1/2/2015
 3               xyz       1/3/2015

Output:

Case 1:Starts with(This is just for name column only.likewise i want to do for HolidayDate column too)
Input:ab(filtering parameter)
Query:where Name like '%ab%' order by Name(when sort column name is  passed as parameter in stored procedure for column to sort(for eg:Name))
output:1,abc,1/1/2015

Case 2:Is Equal to(Same as above)
Input:prr(filtering parameter)
output:2,pqr,1/2/2015

Case 3:Not Equal to(Same as above)
Input:bbb(filtering parameter)
output:All Records

This is my stored procedure so far:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_PagedItems]
    (
     @Page int,
     @RecsPerPage int
    )
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON


--Create a temporary table
CREATE TABLE #TempItems
(
    ID int,
    Name varchar(50),
    HolidayDate date
)


-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (ID, Name,HolidayDate)
SELECT HolidaysId,HolidayDiscription,HolidayDate FROM holiday 

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
       MoreRecords = 
    (
     SELECT COUNT(*) 
     FROM #TempItems TI
     WHERE TI.ID >= @LastRec
    ) 
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec


-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

Now there are 4 things i would send to my stored procedure are:

1. Page no
2. PageSize(number of records to retrive)
3. Sorting Column Name(Name Or HolidayDate)
4. My filter Column name(Name of Holidaydate) and Operator like StartWith or Equal to or not equal to.(ColumnName and Operator)

Can anybody help me to perform sorting and filtering and if any performance optimization related changes is there then please please do suggest me.

Answer 1

I've not tested this, but something like this you can use as starter and do modifications to make it stable:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_PagedItems]
    (
    @ID int = NULL,
    @Name varchar(50) = NULL,
    @HolidayDate date = NULL,
    @SortCol varchar(20) = '',
     @Page int=1,
     @RecsPerPage int=10 -- default size, you can change it or apply while executing the SP
    )
AS
BEGIN
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON


--Create a temporary table
CREATE TABLE #TempItems
(
    ID int,
    Name varchar(50),
    HolidayDate date
)


-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (ID, Name,HolidayDate)
SELECT HolidaysId, HolidayDiscription, HolidayDate 
FROM holiday 

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
; WITH CTE_Results
AS (
    SELECT ROW_NUMBER() OVER (ORDER BY
        CASE WHEN @SortCol = 'ID_Asc' THEN ID
            END ASC,
        CASE WHEN @SortCol = 'ID_Desc' THEN ID
            END DESC, 

        CASE WHEN @SortCol = 'Name_Asc' THEN Name
            END ASC,
        CASE WHEN @SortCol = 'Name_Desc' THEN Name
            END DESC, 

        CASE WHEN @SortCol = 'HolidayDate_Asc' THEN HolidayDate
            END ASC,
        CASE WHEN @SortCol = 'HolidayDate_Desc' THEN HolidayDate
            END DESC
        ) AS ROWNUM,
        ID,
        Name,
        HolidayDate
    FROM #TempItems
    WHERE 
            (@ID IS NULL OR ID = @ID)
        AND (@Name IS NULL OR Name LIKE '%' + @Name + '%')
        AND (@HolidayDate IS NULL OR HolidayDate = @HolidayDate)
    )
SELECT 
        ID,
        Name,
        HolidayDate
    FROM CTE_Results
    WHERE
        ROWNUM > @FirstRec 
    AND ROWNUM < @LastRec
    ORDER BY ROWNUM ASC

-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
END
GO

You can check the blog posts I've written on:

1. Creating Stored Procedures with Dynamic Search (filter)

2. Creating Stored Procedures with Dynamic Search & Paging (Pagination)

3. Creating Stored Procedure with Dynamic Search, Paging and Sorting

4. You can also use the FETCH-OFFSET clause for Pagination if you are on SQL 2012 or more, link.

Answer 2

This is how i have done and i am getting expected output but still i want to take improvement suggestion from all of you if there is any.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[HolidayFetchList]
@pageno int,
@pagesize int,
@sortorder varchar(10),
@sortcolumn varchar(100),
@filter varchar(max),
@count int OUTPUT
AS
BEGIN
    declare @Start int=(@pageno)*@pagesize;
    declare @End int=@Start+@pagesize;
    SET NOCOUNT ON;

    DECLARE @tblHoliday AS TABLE
        (HolidaysId int,HolidayDate date,HolidayDiscription nvarchar(500),HolidayName nvarchar(max),RN int)

    declare @sql varchar(max)= '
    select HolidaysId,HolidayDate,HolidayDiscription,HolidayDiscription as HolidayName,ROW_NUMBER() OVER 
                     (ORDER BY '+@sortcolumn + ' '+@sortorder+' ) AS RN from Holiday
     WHERE 1=1 '+@filter
     print @sql
INSERT INTO @tblHoliday
exec (@sql)
select  @count=COUNT(*) from @tblHoliday
print @count
select * from @tblHoliday where RN>@Start and RN<=@End order by RN 
END

Please do give me any suggestion if you have any.