IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='PageTest')
DROP PROC PageTest
GO
CREATE PROCEDURE [dbo].[PageTest]
@Table VARCHAR(1000), --表名,多表是请使用 tA a inner join tB b On a.AID = b.AID
@TIndex NVARCHAR(100), --主键
@Column NVARCHAR(2000) = '*',--要查询的字段，全部字段就为*
@Sql NVARCHAR(3000) = '',--Where条件
@PageIndex INT = 1, --开始页码
@PageSize INT = 10, --每页查询数据的行数
@Sort NVARCHAR(200) = '' --排序的字段
AS
DECLARE @strWhere VARCHAR(2000)
DECLARE @strsql NVARCHAR(3900)
IF @Sql IS NOT NULL AND len(LTRIM(RTRIM(@Sql)))>0
BEGIN
SET @strWhere = ' WHERE ' + @Sql + ' '
END
ELSE
BEGIN
SET @strWhere = ''
END
IF (charindex(LTRIM(RTRIM(@TIndex)),@Sort)=0)
BEGIN
IF(@Sort='')
SET @Sort = @TIndex + ' DESC '
ELSE
SET @Sort = @Sort+ ' , '+@TIndex + ' DESC '
END
IF @PageIndex < 1
SET @PageIndex = 1
IF @PageIndex = 1
BEGIN
SET @strsql = 'SELECT TOP ' + str(@PageSize) +' '+@Column+ ' FROM ' + @Table + ' ' + @strWhere + ' ORDER BY '+ @Sort
END
ELSE
BEGIN
DECLARE @START_ID NVARCHAR(50)
DECLARE @END_ID NVARCHAR(50)
SET @START_ID = convert(NVARCHAR(50),(@PageIndex - 1) * @PageSize + 1)
SET @END_ID = convert(NVARCHAR(50),@PageIndex * @PageSize)
SET @strsql = ' SELECT '+@Column+ '
FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS RowNum,
'+@Column+ '
FROM '+@Table +' WITH(NOLOCK) ' + @strWhere +') AS D
WHERE RowNum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY '+@Sort
END
EXEC(@strsql)
PRINT @strsql
SET @strsql = 'SELECT Count(1) as TotalRecords FROM ' + @Table +' WITH(NOLOCK) ' + @strWhere
PRINT @strsql
EXEC(@strsql)