17,740
社区成员
发帖
与我相关
我的任务
分享
USE #_$_DBNAME_$_#
GO
/*
功能描述:公共分页存储过程
编写人:乔虎跃
创建日期:2013年06月27日
版 权:Copyright © QiaoHY(乔虎跃)。
*/
IF OBJECT_ID('PROCEDURE_COMMONPAGING') IS NOT NULL
DROP PROCEDURE PROCEDURE_COMMONPAGING
GO
CREATE PROCEDURE PROCEDURE_COMMONPAGING
(
@TOTALRECORDCOUNT INT OUTPUT,--总记录数
@TOTALPAGECOUNT INT OUTPUT, --总页数
@PAGEINDEX INT,--页索引
@PAGESIZE INT,--每页的数量
@INPUTSQLSTRING NVARCHAR(MAX),--输入的SQL语句字符串
@ORDERBY NVARCHAR(256)--要排序的字段名。格式:{Field1 [SortMode(DESC | ASC)]......,n [SortMode(DESC | ASC)]}
)
AS
BEGIN
BEGIN TRY
DECLARE @TRCSQL NVARCHAR(MAX)
DECLARE @RSQL NVARCHAR(MAX)
DECLARE @BEGINPAGEINDEX INT
DECLARE @ENDPAGEINDEX INT
SET @TRCSQL = 'SELECT @TOTALRECORDCOUNT = COUNT(*) FROM ('+@INPUTSQLSTRING+') AS TEMP'
EXECUTE SP_EXECUTESQL @TRCSQL,N'@TOTALRECORDCOUNT INT OUTPUT',@TOTALRECORDCOUNT OUTPUT
--设置分页总页数。
SET @TOTALPAGECOUNT = CEILING((@TOTALRECORDCOUNT+0.0)/@PAGESIZE)
IF (@PAGEINDEX > 1)
BEGIN
IF(@PAGEINDEX > @TOTALPAGECOUNT)
BEGIN
SET @BEGINPAGEINDEX=(@TOTALPAGECOUNT -1) * @PAGESIZE
END
ELSE
BEGIN
SET @BEGINPAGEINDEX=(@PAGEINDEX-1) * @PAGESIZE
END
SET @ENDPAGEINDEX= (@BEGINPAGEINDEX+ @PAGESIZE)
END
ELSE
BEGIN
SET @BEGINPAGEINDEX = 0
SET @ENDPAGEINDEX = @PAGESIZE
END
SET @RSQL ='
SELECT * FROM
(
SELECT TEMP.*,ROW_NUMBER() OVER(ORDER BY '+@ORDERBY+') AS SID
FROM ('+@INPUTSQLSTRING+') TEMP
) T
WHERE T.SID > '+CAST(@BEGINPAGEINDEX AS NVARCHAR)+' AND T.SID <= '+CAST(@ENDPAGEINDEX AS NVARCHAR)+''
EXECUTE sys.SP_EXECUTESQL @RSQL
END TRY
BEGIN CATCH
RETURN SELECT text FROM sys.messages WHERE message_id = @@ERROR AND sys.messages.language_id =2052
END CATCH
END
select [Id]
,[Name]
,[StudentId]
,[MajorId]
from T_Student
order by [Id]
offset 4 rows
fetch next 2 rows only
USE #_$_DBNAME_$_#
GO
/*
功能描述:公共分页存储过程
编写人:乔虎跃
创建日期:2013年06月27日
版 权:Copyright © QiaoHY(乔虎跃)。
*/
IF OBJECT_ID('PROCEDURE_COMMONPAGING') IS NOT NULL
DROP PROCEDURE PROCEDURE_COMMONPAGING
GO
CREATE PROCEDURE PROCEDURE_COMMONPAGING
(
@TOTALRECORDCOUNT INT OUTPUT,--总记录数
@TOTALPAGECOUNT INT OUTPUT, --总页数
@PAGEINDEX INT,--页索引
@PAGESIZE INT,--每页的数量
@INPUTSQLSTRING NVARCHAR(MAX),--输入的SQL语句字符串
@ORDERBY NVARCHAR(256)--要排序的字段名。格式:{Field1 [SortMode(DESC | ASC)]......,n [SortMode(DESC | ASC)]}
)
AS
BEGIN
BEGIN TRY
DECLARE @TRCSQL NVARCHAR(MAX)
DECLARE @RSQL NVARCHAR(MAX)
DECLARE @BEGINPAGEINDEX INT
DECLARE @ENDPAGEINDEX INT
SET @TRCSQL = 'SELECT @TOTALRECORDCOUNT = COUNT(*) FROM ('+@INPUTSQLSTRING+') AS TEMP'
EXECUTE SP_EXECUTESQL @TRCSQL,N'@TOTALRECORDCOUNT INT OUTPUT',@TOTALRECORDCOUNT OUTPUT
--设置分页总页数。
SET @TOTALPAGECOUNT = CEILING((@TOTALRECORDCOUNT+0.0)/@PAGESIZE)
IF (@PAGEINDEX > 1)
BEGIN
IF(@PAGEINDEX > @TOTALPAGECOUNT)
BEGIN
SET @BEGINPAGEINDEX=(@TOTALPAGECOUNT -1) * @PAGESIZE
END
ELSE
BEGIN
SET @BEGINPAGEINDEX=(@PAGEINDEX-1) * @PAGESIZE
END
SET @ENDPAGEINDEX= (@BEGINPAGEINDEX+ @PAGESIZE)
END
ELSE
BEGIN
SET @BEGINPAGEINDEX = 0
SET @ENDPAGEINDEX = @PAGESIZE
END
SET @RSQL ='
SELECT * FROM
(
SELECT TEMP.*,ROW_NUMBER() OVER(ORDER BY '+@ORDERBY+') AS SID
FROM ('+@INPUTSQLSTRING+') TEMP
) T
WHERE T.SID > '+CAST(@BEGINPAGEINDEX AS NVARCHAR)+' AND T.SID <= '+CAST(@ENDPAGEINDEX AS NVARCHAR)+''
EXECUTE sys.SP_EXECUTESQL @RSQL
END TRY
BEGIN CATCH
RETURN SELECT text FROM sys.messages WHERE message_id = @@ERROR AND sys.messages.language_id =2052
END CATCH
END
select [Id]
,[Name]
,[StudentId]
,[MajorId]
from T_Student
order by [Id]
offset 4 rows
fetch next 2 rows only