sql 2005 分页存储过程

sql 2005 分页存储过程

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- [SelectBase] 1,1,'Users','username=''test'''ALTER procedure [dbo].[SelectBase]@PageIndex int,@PageSize int,@TableName nvarchar(max),@Where nvarchar(max)=''asDeclare @rowcount intDeclare @intStart intDeclare @intEnd intDeclare @SQl nvarchar(max), @WhereR nvarchar(max), @OrderBy nvarchar(max)set @rowcount=0set nocount onif @Where<>''begin set @Where=' and '+@Whereendif CHARINDEX('order by', @Where)>0begin set @WhereR=substring(@Where, 1, CHARINDEX('order by',@Where)-1) --取得条件 set @OrderBy=substring(@Where, CHARINDEX('order by',@Where), Len(@Where)) --取得排序方式(order by 字段 方式)endelsebegin set @WhereR=@Where declare @PKName nvarchar(50) if(len(@TableName)>50) begin Set @PKName='ID' end else begin select top 1 @PKName=[name] from syscolumns where id=object_id(@TableName) order by colstat desc end set @OrderBy=' order by '+@PKName+' asc'endset @SQl='SELECT @rowcount=count(*) from '+cast(@TableName as nvarchar(3000))+' where 1=1 '+@WhereRexec sp_executeSql @SQl,N'@rowcount int output',@rowcount outputif @PageIndex=0 and @PageSize=0 --不进行分页,查询所有数据列表begin set @SQl='SELECT * from '+cast(@TableName as nvarchar(3000))+' where 1=1 '+@Whereendelse --进行分页查询数据列表begin set @intStart=(@PageIndex-1)*@PageSize+1; set @intEnd=@intStart+@PageSize-1 set @SQl='select * from(select *,ROW_NUMBER() OVER('+cast(@OrderBy as nvarchar)+') as row from ' set @SQl=@SQL+@TableName+' where 1=1 '+@WhereR+') as a where row between '+cast(@intStart as varchar)+' and '+cast(@intEnd as varchar)end--print @SQlexec sp_executeSql @SQl--select @rowcountreturn @rowcount----------------------------------------------print @SQl--exec [SelectBase] 1,8,'SpaceContent','UserInfoID=45'set nocount off

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。