oracle动态sql分页,sqlserver支持定位当前页,自定义排序的分页SQL(拒绝动态SQL)

1,场景：根据学生编号查询，返回该学生所在班级的所有学生。支持分页、自定义排序及结果集自动定位到查询条件的学生编号所在页。

代码如下:

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 a

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 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,简单条件的,动态where语句(关于Like查询的动态where，建议使用笨办法做)

代码如下:

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

您可能感兴趣的文章:基于Oracle的高性能动态SQL程序开发

为什么ASP中执行动态SQL总报错误信息？提示语句语法错误

批处理 动态sql

动态SQL语句使用心得

编程经验点滴 动态SQL的拼接技巧

oracle中动态SQL使用详细介绍

mysql存储过程 在动态SQL内获取返回值的方法详解

MySQL 存储过程中执行动态SQL语句的方法

mybatis的动态sql详解(精)