Sql Server通用分页存储过程

--处理开始点和结束点 Declare @StartRecord int; Declare @EndRecord int;  Declare @TotalCountSql nvarchar(1200);  Declare @SqlString nvarchar(4000);   

--统计记录 if(@TotalRecord is null OR @TotalRecord>=0) begin SET @TotalCountSql= N‘select @TotalRecord = count(*) from ‘ + @TableName +‘ where ‘+@WhereClause;  --select @TotalCountSql EXEC sp_executesql @totalCountSql,N‘@TotalRecord int out‘,@TotalRecord output;--返回总记录数 end

if @PageSize>0 begin     if @PageIndex<1 set @PageIndex=1 set @StartRecord = (@PageIndex-1)*@PageSize + 1     set @EndRecord = @StartRecord + @PageSize - 1  set @SqlString = N‘select row_number() over (order by ‘+ @OrderClause +‘) as rowId,‘+@ColumnNames+‘ from ‘+ @TableName+‘ where ‘+@WhereClause; set @SqlString =‘select * from (‘ + @SqlString + ‘) as t where rowId between ‘ + ltrim(str(@StartRecord)) + ‘ and ‘ + ltrim(str(@EndRecord)); end else  begin set @SqlString=‘select ‘+@ColumnNames+‘ from ‘+ @TableName+‘ where ‘+@WhereClause +‘ order by ‘+@OrderClause end --select @SqlString Exec(@SqlString) END

Sql Server通用分页存储过程

标签：