--翻页显示
create procedure select_paging_table
@tableName nvarchar(max),
@primaryKey nvarchar(max),--主键
@pageIndex int,
@selectFieldsName nvarchar(max),--查询的字段
@top int,--每页显示多少条
@where nvarchar(max),
@orderBy nvarchar(max),
@other nvarchar(max)
as
declare @_top nvarchar(max)='',@_selectFieldsName nvarchar(max)='',@_where nvarchar(max)='',@_orderBy nvarchar(max)=''
set @_selectFieldsName=' * '
if(@top<>0)
begin
set @_top=' top '+convert(nvarchar(50),@top)+' '
end
if(@selectFieldsName<>'' and @selectFieldsName is not null)
begin
set @_selectFieldsName=@selectFieldsName
end
if(@where<>'' and @where is not null)
begin
set @_where=' where '+@where+' '
end
if(@orderBy<>'' and @orderBy is not null)
begin
set @_orderBy=' order by '+@orderBy+' '
end
declare @sql nvarchar(max)
set @sql='select '+@_top+@_selectFieldsName+' from '+@tableName
if(@pageIndex=1)
begin
set @sql+=@_where+@_orderBy+@other
end
else if(@pageIndex>1)
begin
set @sql+=' where '+@primaryKey+' not in(select top '+Convert(nvarchar(50),@top*(@pageIndex-1))+' '+@primaryKey+' from '+@tableName+@_where+@_orderBy+@other+' ) '
if(@where<>'')
begin
set @sql+=' and '+@where
end
set @sql+=@_orderBy+@other
end
exec (@sql)
返回结果直接使用就OK了 不足之处 多多指教