jQuery datatables stored procedure for paging sorting and searching

Suggested Videos
Part 106 - jQuery datatables get data from database table
Part 107 - jQuery datatables individual column search
Part 108 - jQuery datatable show hide columns



In this video we will discuss implementing a stored procedure that can perform paging sorting and searching. In our upcoming videos we will discuss writing a generic handler that calls this stored procedure. The generic handler will convert the data to JSON format. The JSON formatted data can then be consumed by the jQuery datatables plugin. All the processing, that is, paging, sorting and searching is done on the server side.



If the dataset is small you can retrieve all the data at once and all the processing (i.e paging, searching, sorting) can be done on the client-side. We discussed this in Part 106 for jQuery tutorial.

However, if the dataset is large (i.e if you have millions of records), loading all data at once is not the best thing to do from a performance standpoint. With large dataset, we only want to load the correct set of rows (sorted, paged and filtered data ) that want to display on the page. So all the processing ((i.e paging, searching and sorting) should be done by the server. So in this and our upcoming videos we will discuss performing server side processing.

We want to retrieve data from the following database table tblEmployee 


SQL Script to create the table and populate it with test data

Create table tblEmployees

(

    ID int primary key identity,

    FirstName nvarchar(50),

    LastName nvarchar(50),

    Gender nvarchar(20),

    JobTitle nvarchar(20)

)

Go

Insert into tblEmployees values('Mark', 'Hastings','Male','Developer')

Insert into tblEmployees values('Maria', 'Nicholas','Female','Developer')

Insert into tblEmployees values('Robert', 'Stephenson','Male','Sr. Developer')

Insert into tblEmployees values('Mary', 'Quant','Female','Sr. Developer')

Insert into tblEmployees values('John', 'Stenson','Male','Sr. Developer')

Insert into tblEmployees values('Gilbert', 'Sullivan','Male','Developer')

Insert into tblEmployees values('Rob', 'Gerald','Male','Sr. Developer')

Insert into tblEmployees values('Ron', 'Simpson','Male','Developer')

Insert into tblEmployees values('Sara', 'Solomon','Female','Sr. Developer')

Insert into tblEmployees values('Rad', 'Wicht','Male','Sr. Developer')

Insert into tblEmployees values('Julian', 'John','Male','Developer')

Insert into tblEmployees values('James', 'Bynes','Male','Sr. Developer')

Insert into tblEmployees values('Mary', 'Ward','Female','Developer')

Insert into tblEmployees values('Michael', 'Niron','Male','Sr. Developer')

SQL Server stored procedure for paging sorting and searching

create proc spGetEmployees

@DisplayLength int,

@DisplayStart int,

@SortCol int,

@SortDir nvarchar(10),

@Search nvarchar(255) = NULL

as

begin

    Declare @FirstRec int, @LastRec int

    Set @FirstRec = @DisplayStart;

    Set @LastRec = @DisplayStart + @DisplayLength;

   

    With CTE_Employees as

    (

         Select ROW_NUMBER() over (order by

        

         case when (@SortCol = 0 and @SortDir='asc')

             then Id

         end asc,

         case when (@SortCol = 0 and @SortDir='desc')

             then Id

         end desc,

        

        case when (@SortCol = 1 and @SortDir='asc')

             then FirstName

        end asc,

        case when (@SortCol = 1 and @SortDir='desc')

            then FirstName

        end desc,

        case when (@SortCol = 2 and @SortDir='asc')

            then LastName

        end asc,

        case when (@SortCol = 2 and @SortDir='desc')

            then LastName

        end desc,

        case when (@SortCol = 3 and @SortDir='asc')

            then Gender

        end asc,

        case when (@SortCol = 3 and @SortDir='desc')

            then Gender

        end desc,

        case when (@SortCol = 4 and @SortDir='asc')

            then JobTitle

        end asc,

        case when (@SortCol = 4 and @SortDir='desc')

            then JobTitle

        end desc)

         as RowNum,

         COUNT(*) over() as TotalCount,

         Id,

         FirstName,

         LastName,

         Gender,

         JobTitle

         from tblEmployees

         where (@Search IS NULL

                 Or Id like '%' + @Search + '%'

                 Or FirstName like '%' + @Search + '%'

                 Or LastName like '%' + @Search + '%'

                 Or Gender like '%' + @Search + '%'

                 Or JobTitle like '%' + @Search + '%')

    )

    Select *

    from CTE_Employees

    where RowNum > @FirstRec and RowNum <= @LastRec

end

Finally test the stored procedure