Create stored procedure for paging sorting and searching jQuery DataTables

By Unknown - Friday, September 11, 2015 - No Comments

(Create stored procedure for paging sorting and searching in SQL Server) -Đối với những bảng dữ liệu có số lượng bản ghi nhỏ thì chuyện xử lý khi hiển thị không quá bận tâm, bạn có thể lấy đồng thời tất cả dữ liệu và xử lý trên client-side. Tuy nhiên với những bảng dữ liệu lớn có đến hàng triệu bản ghi thì việc xử lý khi hiển thị sẽ phải đặc biệt lưu ý đến tốc độ xử lý, đặc biệt khi thực hiện các thao tác như: sắp xếp, phân trang, tìm kiếm dữ liệu. Nếu như việc xử lý không tối ưu sẽ làm người dùng có cảm giác khó chịu mỗi khi thao tác do mất quá nhiều thời gian. Vậy có giải pháp tối ưu nào để lấy dữ liệu trong 1 bảng có số lượng bản ghi lớn không?

Bài viết dưới đây sẽ hướng dẫn các bạn viết 1 stored procedure để lấy dữ liệu từ bảng Customers trong CSDL Northwind. Mỗi khi thực hiện stored procedure này, nếu không có giá trị tìm kiếm nào được đưa vào thì nó cũng không lấy toàn bộ các bản ghi có trong bảng. Số lượng bản ghi sẽ được lấy căn cứ vào chính đối số @DisplayLength được truyền vào và đây thường chính là số bản ghi/trang. Như vậy với số lượng bản ghi được giới hạn như vậy mỗi khi được yêu cầu thì cho dù số lượng bản ghi có lớn đến mấy thì tốc độ xử lý vẫn sẽ được đảm bảo.

Đây là cấu trúc bảng Customers trong CSDL Northwind

STT	Tên trường	Kiểu trường	Ghi chú
1	CustomerID	Int	Trường tự tăng
2	CompanyName	nvarchar(50)
3	ContactName	nvarchar(50)
4	ContactTitle	nvarchar(50)
5	Address	nvarchar(150)
6	City	nvarchar(50)
7	Region	nvarchar(50)
8	PostalCode	nvarchar(50)
9	Country	nvarchar(50)
10	Phone	nvarchar(24)
11	Fax	nvarchar(24)

1. Download CSDL Northwind tại đây và thực hiện công việc Restore Data.

2. Tạo stored procedure

CREATE proc [dbo].[Pro_Customers_List]

@DisplayStart int=0,

@DisplayLength int=10,

@Keyword nvarchar(255) = NULL,

@SortField nvarchar(50)='CompanyName asc'

declare @strSQL nvarchar(max)

declare @strSubSQL nvarchar(3500)

declare @strWhere nvarchar(3500)

declare @strSubWhere nvarchar(3500)

set @strSQL= 'SELECT * FROM '

set @strSubSQL= '(Select ROW_NUMBER() over (order by ' + @SortField + ') as RowNumber,COUNT(*) over() as TotalDisplayRows, (SELECT COUNT(*) FROM Customers) AS TotalRecords,CustomerID,CompanyName,ContactName,ContactTitle,Address, City,Region,PostalCode from Customers'

set @strSubWhere =' where 1=1'

if @Keyword<>''

set @strSubWhere= @strSubWhere +' And (CompanyName like N''%' +@Keyword+'%''

Or ContactName like N''%' +@Keyword+'%'' Or ContactTitle like N''%' +@Keyword+'%''

Or Address like N''%' +@Keyword+'%'' Or City like N''%' +@Keyword+'%'')'

set @strSubWhere= @strSubWhere +') Customers'

set @strSubSQL= @strSubSQL + @strSubWhere

set @strSQL=@strSQL + @strSubSQL

set @strWhere= ' Where 1=1'

set @strWhere= @strWhere +' And RowNumber > ' + convert(nvarchar,@DisplayStart) + ' AND RowNumber <=' + convert(nvarchar,@DisplayStart + @DisplayLength)

set @strSQL=@strSQL+@strWhere

print @strSQL

exec sp_executesql @strSQL

Bạn có thể tải về bảng cơ sở dữ liệu SQL bằng cách nhấn vào liên kết tải về dưới đây

3. Chạy stored procedure

- B1: Kích chuột phải vào stored -> Chọn Execute stored procedure

- B2: Nhập các thông tin cho các đối số của stored như hình phía dưới

- B3: Chọn OK, bạn sẽ có kết quả phía dưới

Chúc các bạn thành công!

Quang Bình

News Ticker