通用分页存储过程

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