通用存储过程分页
2011-03-31 09:46 yuejianjun 阅读(234) 评论(0) 编辑 收藏 举报USE [Test]
GO
/****** Object: StoredProcedure [dbo].[PROC_INFO_LIST_PAGESIZE] Script Date: 03/31/2011 09:41:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 2010.12.17
-- Description: 用于一般用途的存储过程分页
-- =============================================
ALTER PROC [dbo].[PROC_INFO_LIST_PAGESIZE]
@COLUMNNAME VARCHAR(8000), --查询列名称
@ORDERNAME VARCHAR(8000), --排序列名称
@ORDER VARCHAR(10)=NULL, --显示顺序(升序为ASC,降底为DESC,默认为ASC)
@TABLENAME VARCHAR(200), --数据源名称
@PAGEINDEX INT, --结果集页序号
@PAGESIZE INT, --每页容量
@STRWHERE VARCHAR(8000)=NULL --分页条件
AS
DECLARE @RUNSQL NVARCHAR(MAX)
SET NOCOUNT ON
SET @RUNSQL=N'WITH PAGECONTENT AS'
IF ISNULL(@ORDER,'')=''
BEGIN
SET @RUNSQL=@RUNSQL+N' (SELECT '+@COLUMNNAME+',ROW_NUMBER() OVER(ORDER BY '+@ORDERNAME+' ASC) AS ROWNUMBER FROM '+@TABLENAME
END
ELSE
BEGIN
SET @RUNSQL=@RUNSQL+N' (SELECT '+@COLUMNNAME+',ROW_NUMBER() OVER(ORDER BY '+@ORDERNAME+' '+@ORDER+') AS ROWNUMBER FROM '+@TABLENAME
END
IF(ISNULL(@STRWHERE,'')='')
BEGIN
SET @RUNSQL=@RUNSQL+') '
SET @RUNSQL=@RUNSQL+N'SELECT * FROM PAGECONTENT WHERE ROWNUMBER>'+STR((@PAGEINDEX-1)*@PAGESIZE)+' AND ROWNUMBER<='+STR(@PAGEINDEX*@PAGESIZE)+';'
SET @RUNSQL=@RUNSQL+N'SELECT COUNT(1) AS TOTALNUMBER FROM '+@TABLENAME +' '
print @runsql
END
ELSE
BEGIN
SET @RUNSQL=@RUNSQL+' WHERE '+@STRWHERE+') '
SET @RUNSQL=@RUNSQL+N'SELECT * FROM PAGECONTENT WHERE ROWNUMBER>'+STR((@PAGEINDEX-1)*@PAGESIZE)+' AND ROWNUMBER<='+STR(@PAGEINDEX*@PAGESIZE)+';'
SET @RUNSQL=@RUNSQL+N'SELECT COUNT(1) AS TOTALNUMBER FROM '+@TABLENAME +' WHERE '+@STRWHERE
print @runsql
END
EXEC SP_EXECUTESQL @RUNSQL