SQL Server分页存储过程

CREATE proc [dbo].[USP_Web_Interface_Pagination]
	@TableList VARCHAR(3000)='*',--搜索表的字段,比如：’id，datatime，job‘，用逗号隔开
	@TableName VARCHAR(30), --查询的表名
	--@TableFrom VARCHAR(1000),--表后面跟着的在where前面的一些条件
	@SelectWhere VARCHAR(500)='',--搜索条件，这里不用写where，比如：job=’123‘and order='2'
	@SelectOrderId VARCHAR(20),--表主键字段名。比如：id
	@SelectOrder VARCHAR(500)='', --排序，可以使用多字段排序但主键字段必需在最前面.也可以不写，比如：order by creatTime asc
	@IntPageNo INT=1, --页号
	@IntPageSize INT=10 ,--每页显示数
	@RecordCount INT OUTPUT  --总记录数(存储过程输出参数)
AS  
BEGIN
	DECLARE @TmpSelect NVARCHAR(600)  
	--DECLARE @Tmp NVARCHAR(600)  
	DECLARE @temp_select VARCHAR(2000)  
	DECLARE @temp_from VARCHAR(2000)
	DECLARE @temp_where VARCHAR(2000)
	DECLARE @temp_in VARCHAR(2000) 
	DECLARE @temp_end VARCHAR(1000) 
	DECLARE @temp_T_sql NVARCHAR(MAX)  

	SET NOCOUNT ON--关闭计数
	
	IF(@SelectWhere != '')
	BEGIN
		SET @TmpSelect = 'select @RecordCount = count(*) from '+@TableName+' WHERE '+@SelectWhere
	END
	ELSE
	BEGIN
		SET @TmpSelect = 'select @RecordCount = count(*) from '+@TableName
	END
	
	EXECUTE sp_executesql 
	@TmpSelect,    --执行上面的sql语句
	N'@RecordCount int OUTPUT' ,   --执行输出数据的sql语句，output出总记录数
	@RecordCount  OUTPUT
	IF (@RecordCount = 0)    --如果没有，则返回零
		return 0
       
	/*判断页数是否正确*/
	IF (@IntPageNo - 1) * @IntPageSize > @RecordCount   --页号大于总页数，返回错误
		return (-1)
	SET NOCOUNT OFF--打开计数
	IF @SelectWhere != '' 
	BEGIN	
		--SET @TmpSelect = 'select top '+str(@IntPageSize)+' '+@TableList+' from '+@TableName+' where '+@SelectOrderId+' not in(select top '+str((@IntPageNo-1)*@IntPageSize)+' '+@SelectOrderId+' from '+@TableName+' where '+@SelectWhere +' '+@SelectOrder+') and '+@SelectWhere +' '+@SelectOrder
		SET @Temp_Select = 'select top '+ Convert(VARCHAR(20),@IntPageSize) +' '+@TableList;
		SET @Temp_From = ' from '+@TableName;
		SET @temp_where = ' where '+@SelectOrderId;
		SET @Temp_In = ' not in(select top '+Convert(VARCHAR(20),(@IntPageNo-1)*@IntPageSize)+' '+@SelectOrderId+' from '+@TableName+' where '+@SelectWhere +' '+@SelectOrder+') ';
		SET @Temp_End = ' and '+@SelectWhere +' '+@SelectOrder;
		
		
	END
	ELSE
	BEGIN
		--SET @TmpSelect = 'select top '+str(@IntPageSize)+' '+@TableList+' from '+@TableName+' where '+@SelectOrderId+' not in(select top '+str((@IntPageNo-1)*@IntPageSize)+' '+@SelectOrderId+' from '+@TableName+' '+@SelectOrder+') '+@SelectOrder
		SET @Temp_Select = 'select top '+Convert(VARCHAR(20),@IntPageSize)+' '+@TableList;
		SET @Temp_From = ' from '+@TableName
		SET @temp_where = ' where '+@SelectOrderId;
		SET @Temp_In = ' not in(select top '+Convert(VARCHAR(20),(@IntPageNo-1)*@IntPageSize)+' '+@SelectOrderId+' from '+@TableName+' '+@SelectOrder+') ';
		SET @Temp_End = @SelectOrder;
	END
	
	--RETURN @Temp_Select + @Temp_From + ' ' +@TableFrom + @temp_where + @Temp_In + @Temp_End 
	--EXECUTE sp_executesql @TmpSelect
	SET @temp_T_sql = @Temp_Select + @Temp_From + ' ' +@TableFrom + @temp_where + @Temp_In + @Temp_End 
	--RETURN @temp_T_sql
	--Exec(@Temp_Select + @Temp_From + ' ' +@TableFrom + @temp_where + @Temp_In + @Temp_End )
	Exec SP_EXECUTESQL @temp_T_sql
	RETURN(@@rowcount)
END