sql 2000 分页存储过程

sql 2000 分页存储过程

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- [SelectBase] 1,1,'( select j.*,u.P_Name from Job j left join Users u on j.UserID=u.ID) T','Flag=0' alter procedure [SelectBase] @PageIndex int, @PageSize int, @TableName nvarchar(2000), @Where nvarchar(2000)='' as Declare @rowcount int Declare @intStart int Declare @intEnd int declare @Column1 varchar(32) --第一列名称 Declare @SQl nvarchar(2000), @WhereR nvarchar(1000), @OrderBy nvarchar(1000) set @rowcount=0 set nocount on if @Where<>'' begin set @Where=' and '+@Where end if CHARINDEX('order by', @Where)>0 begin set @WhereR=substring(@Where, 1, CHARINDEX('order by',@Where)-1) --取得条件 set @OrderBy=substring(@Where, CHARINDEX('order by',@Where), Len(@Where)) --取得排序方式(order by 字段 方式) end else begin set @WhereR=@Where set @OrderBy=' order by id asc' end set @SQl='SELECT @rowcount=count(*) from '+cast(@TableName as nvarchar(2000))+' where 1=1 '+@WhereR exec sp_executeSql @SQl,N'@rowcount int output',@rowcount output if @PageIndex=0 and @PageSize=0 --不进行分页,查询所有数据列表 begin set @SQl='SELECT * from '+cast(@TableName as nvarchar(2000))+' where 1=1 '+@Where end else --进行分页查询数据列表 begin set @intStart=(@PageIndex-1)*@PageSize+1; set @intEnd=@intStart+@PageSize-1 declare @PKName nvarchar(50) if(len(@TableName)>50) begin Set @Column1='ID' end else begin set @Column1=col_name(object_id(@tableName),1) --设置第一列名称 end set @SQl='Create table #tem(Row int identity(1,1) not null,joinRow int) ' set @SQl=@SQl+'insert #tem(joinRow) select '+@Column1+' from '+@TableName +' where 1=1 '+@WhereR set @SQl=@SQl+' select * from '+@TableName+' right join #tem on '+@Column1+'=#tem.joinRow ' set @SQl=@SQl+' where #tem.Row between '+cast(@intStart as varchar)+' and '+cast(@intEnd as varchar) end --PRINT @SQl exec sp_executeSql @SQl return @rowcount set nocount off

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