Sql Server2005通用分页存储过程
CREATE PROCEDURE [dbo].[Common_GetPagedList]
(
@TableName nvarchar(100), --表名
@ColumnNames nvarchar(1000) = *, --字段名集合(全部字段为*，其它逗号分隔)
@OrderClause nvarchar(1000), --排序从句(不包含order by)
@WhereClause nvarchar(1000) =N 1=1 , --条件从句(不包含where)
@PageSize int = 0, --每页记录数(0为所有)
@PageIndex int = 1, --页索引(从1开始)
@TotalRecord int output --返回总记录数
)
AS
BEGIN
if (@ColumnNames is null or @ColumnNames=) set @ColumnNames= *
if (@WhereClause is null or @WhereClause=) set @WhereClause= 1=1
if (@OrderClause is null or @OrderClause=) set @OrderClause= Id desc
--处理开始点和结束点
Declare @StartRecord int;
Declare @EndRecord int;
Declare @TotalCountSql nvarchar(1200);
Declare @SqlString nvarchar(4000);
--统计记录
if(@TotalRecord is null OR @TotalRecord>=0)
begin
SET @TotalCountSql= Nselect @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 = Nselect 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
