create PROCEDURE [dbo].[GetPageChangingTableByRowNumber]
@Field nvarchar(1000),
@TableName nvarchar(100),
@condition nvarchar(1000),--格式为：and (查询条件) 如'and (key=value and key1=value1)'
@OrderField nvarchar(100),
@OrderType int,
@pageindx int,
@PageSize int,
@RecordCount int output --记录的总数
as
BEGIN
--判断是否有排序字段

if(@OrderField is null or ltrim(rtrim(@OrderField))='')
begin
RAISERROR('排序字段不能为空',16,1)
return
end

--组装order语句
declare @temp nvarchar(200)
set @temp=' order by '+@OrderField
if(@OrderType=1)
begin
set @temp=@temp+' asc '
end
else
begin
set @temp=@temp+' desc '
end

--组装查询条件，如果没有查询条件，直接跳过
if(@condition is not null and ltrim(rtrim(@condition))!='')
begin
set @condition='where 1=1'+@condition
end
else
begin
set @condition=''
end

--求记录的总数
declare @Countsql nvarchar(max)
set @Countsql='select @a= count(1) from '+@TableName +' '+@condition
exec sp_executesql @Countsql,N'@a int output',@RecordCount output

print @RecordCount


declare @sql nvarchar(max)

--分页
if(@pageindx=1)
begin
set @sql=' select top '+cast(@pagesize as nvarchar )+' '+ @Field+' from '+@TableName +' '+@condition+' '+@temp
end
else
begin
declare @startNumber int
set @startNumber =(@pageindx-1)*@pagesize
set @sql='select ROW_NUMBER() over('+@temp+') as number, '+@Field+' from '+@TableName+' '+@condition
set @sql='SET ROWCOUNT '+Convert(varchar(4),@PageSize)+'; WITH SP_TABLE AS('+@sql +') SELECT '+@Field+' from SP_TABLE where number>'+CAST(@startNumber as nvarchar)
end
print @sql
exec(@sql)



END



本文来自于博客园 作者suchen