1, scene: According to the student number query, return the student in the class of all students. The page that supports paging, custom sorting, and the student number where the result set is automatically positioned to the query criteria.
Copy Code code 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 () + (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 the 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 a
As (the SELECT top (@EndNumber-@StartNumber) ID,
RowNumber
From (The 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 A
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, simple conditions, dynamic where statement (about the like query dynamic where, recommended to use stupid method)
Copy Code code as follows:
CREATE PROC [dbo].[ Getstudentlistbycondition] @Name NVARCHAR,
@Class INT
as
SET nocount on
begin
Begin
select [Name],
[class]
from [testtable]
WHERE [Class] = case
@Class > 0 THEN @Class ELSE [Class] End
and [name] = Case
When @Name <> "THEN @Name ELSE [name] End
End
End
