Sqlserver supports locating the current page and customizing sorted paging SQL statements (rejecting dynamic SQL statements)
Sqlserver supports locating the current page and customizing sorted paging SQL statements (rejecting dynamic SQL statements)
1. Scenario: query by student ID and return all students in the student's class. You can automatically locate the student ID page in the query condition by PAGE, custom sorting, and result set.
The Code is as follows:
Create proc [dbo]. [Sp_testpagerandsorting] (@ GroupID INT,
@ CurrentId INT,
@ TimeFrom DATETIME,
@ TimeTo DATETIME,
@ OrderBy CHAR (50 ),
@ PageSize INT,
@ CurrentPage INT)
AS
SET nocount ON
BEGIN
DECLARE @ StartNumber INT,
@ EndNumber INT,
@ CurrentIdRowNumber INT,
@ RecordCount INT,
@ EndPageIndex INT
DECLARE @ RowNumberTable TABLE (
Rownumber int identity (1, 1 ),
Id INT)
-- Step 1: Build sort id list -------------------------------------------------------
Insert into @ RowNumberTable
(Id)
SELECT sm. id AS id
FROM dbo. test sm WITH (nolock)
WHERE indate BETWEEN Coalesce (@ TimeFrom, indate) AND
Coalesce (@ TimeTo, indate)
AND sm. groupid = @ GroupID
ORDER BY CASE
WHEN @ OrderBy = 'indate desc' THEN (Row_number () OVER (order by InDate desc ))
WHEN @ OrderBy = 'indate asc 'THEN (Row_number () OVER (order by InDate ASC ))
WHEN @ OrderBy = 'id asc 'THEN (Row_number () OVER (order by sm. Id ASC ))
WHEN @ OrderBy = 'id desc 'THEN (Row_number () OVER (order by sm. Id DESC ))
WHEN @ OrderBy = 'name asc 'THEN (Row_number () OVER (order by sm. Name ASC ))
WHEN @ OrderBy = 'name desc' THEN (Row_number () OVER (order by sm. Name desc ))
END
-- Step 2: Reset page index with current id -----------------------------------------
IF @ CurrentIdNumber> 0
BEGIN
Select top 1 @ CurrentIdRowNumber = rownumber
FROM @ RowNumberTable
WHERE id = @ CurrentIdNumber
IF @ CurrentIdRowNumber> 0
BEGIN
IF @ CurrentPage = 0
BEGIN
SET @ CurrentPage = Ceiling (CAST (@ CurrentIdRowNumber as decimal)/CAST (@ PageSize as decimal ))
END
END
END
ELSE
BEGIN
IF @ CurrentPage = 0
BEGIN
SET @ CurrentPage = 1
END
END
-- Step 3: Set recordCount -----------------------------------------
SELECT @ RecordCount = COUNT (1)
FROM @ RowNumberTable
-- Step 4: Calc startNumber & endNumber -----------------------------------------
SELECT @ StartNumber = @ PageSize * (@ CurrentPage-1 ),
@ EndNumber = @ PageSize * (@ CurrentPage-1) + @ pageSize,
@ EndPageIndex = Ceiling (CAST (@ RecordCount as decimal)/CAST (@ PageSize as decimal ))
IF @ CurrentPage = @ EndPageIndex
BEGIN
SET @ EndNumber = @ RecordCount
END
-- Step 5: Get sorted id of current page -----------------------------------------
; WITH
AS (select top (@ EndNumber-@ StartNumber) id,
Rownumber
FROM (select top (@ EndNumber) id,
Rownumber
FROM @ RowNumberTable) AS B
Order by rownumber DESC)
-- Step 6: Return current page idList -------------------------------------------------------
SELECT [ID],
[GroupID] [Name],
[Address]
FROM dbo. test sm WITH (nolock)
Inner join
ON a. id = sm. id
Order by a. rownumber
-- Step 7: return current page & record count ----------------------------------
SELECT @ CurrentPage AS currentpage,
@ RecordCount AS recordcount
END
2. A simple and dynamic where Statement (we recommend that you use a stupid method for dynamic where statements of Like queries)
The Code is as follows:
Create proc [dbo]. [Getstudentlistbycondition] @ Name NVARCHAR (20 ),
@ Class INT
AS
SET nocount ON
BEGIN
BEGIN
SELECT [Name],
[Class]
FROM [testtable]
WHERE [Class] = CASE
WHEN @ Class> 0 THEN @ Class ELSE [Class] END
AND [name] = CASE
WHEN @ Name <> ''then @ Name ELSE [Name] END
END
END
