Sqlserver supports locating the current page and customizing sorted paging SQL statements (rejecting dynamic SQL statements)

Source: Internet
Author: User

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.
Copy codeThe 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)
Copy codeThe 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.