SQLSERVER分页通用存储过程

USE [DataBase]
GO
/****** Object: StoredProcedure [dbo].[CommonPagenation] Script Date: 06/28/2016 10:41:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[CommonPagenation]
@tableField varchar(200) = '*', --搜索表的字段
@tableName varchar(50) = '', --搜索表名
@selectWhere varchar(500) = '', --搜索条件
@selectID varchar(20)='', --表主键字段名，比如ID
@selectOrder varchar(200)='', --排序，可以使用多字段排序但主键字段必需在最前面.也可以不写，
--比如：order by CreateDate desc
@pageNo int = 1, --页号
@pageSize int = 2 --每页显示记录数
--@totalCount int OUTPUT --总记录数，输出参数
AS
declare @tmpSelect nvarchar(600)

if(@selectWhere !='')
begin
set @tmpSelect = 'select top ' +cast(@pageSize as varchar(20))+' ' +@tableField+' from ' +@tableName+' where ' +@selectID+ ' not in(select top ' +cast(((@pageNo-1)*@pageSize) as varchar(20))
+' ' +@selectID+' from '+@tableName+ ' where ' +@selectWhere+' '+@selectOrder+') and '+@selectWhere+' ' + @selectOrder
end
else
begin
set @tmpSelect = 'select top ' +cast(@pageSize as varchar(20))+' ' +@tableField+' from ' +@tableName+' where ' +@selectID+ ' not in(select top ' +cast(((@pageNo-1)*@pageSize) as varchar(20))+' ' +@selectID+' from '+@tableName+
' '+@selectOrder+') ' + @selectOrder
end
execute sp_executesql @tmpSelect
return(@@rowcount)