jQuery datatables stored procedure for paging sorting and searching

Link for all dot net and sql server video tutorial playlists

Link for slides, code samples and text version of the video

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

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.

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 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

then Id
end asc,
then Id
end desc,

then FirstName
end asc,
then FirstName
end desc,

then LastName
end asc,
then LastName
end desc,

then Gender
end asc,
then Gender
end desc,

then JobTitle
end asc,
then JobTitle
end desc
)
as RowNum,
COUNT(*) over() as TotalCount,
Id,
FirstName,
LastName,
Gender,
JobTitle
from tblEmployees
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

[LetCode]

Venkat, this is probably the time im saying, U R THE BEST !!! always with the most important tutorials ! Thanks again

[imadabab]

Videos 109, 110, 111 and 112 are excellent. Thanks a lot. Appreciate if you add another video number 114 explaining how to implement customer column search with server side jquery datatable. This is will be perfect.

[sajjansarkar]

very nice, only one piece missing though, if we do type a search term, datatables needs an additional field to show the total unfiltered rows. This will allow it to display "Showing 1 to 10 of 138 entries (filtered from *7, 506* total entries)"

the missing variable here is the 7506 one, I got around this by doing a separate SELECT COUNT(*) without the @Search condition.

[ronchennai]

Thanks Venkat.  Hoping your next series will be AngularJS... :)

[saikiran]

Hello Sir, excellent explanation. Could you please make an application to show data on bootstrap table with server side pagination and search functionality.? Can you please do that with returning json data. Thanks in Advance

[gurpreetsingharora]

Can we do the same thing using sql query without using Stored Procedure

[AsimMughal]

Hello Sir, did you have any demo for inline jQuery editable datatable on youtube? I would appreciate it :)

[RSdevLife]

Where is the part where you code it in the jquery? I'm interested in how to code it on the client side and how it handles the paging, sorting and searching. thanks!

[gurpreetsingharora]

Can someone help me to do the same thing but without using stored procedure

[zxcvbnm]

Works fine but slow with large tables
I have a table with 2 millions records, it takes 20 seconds to execute spGetData 1000, 0,0,'desc'
would you please let me know how can i enhance it more

[josephregallis]

When I test the stored procedure with spGetEmployees 10, 0, 1, 'asc', 'Male'
I don't get all 'Male' employees.