CREATE PROCEDURE QueryPage
-- Add the parameters for the stored procedure here
@table varchar(50),
@fields varchar(5000) = '*', --字段名(全部字段为*)
@order varchar(5000), --排序字段(必须!支持多字段)
@where varchar(5000) = Null,--条件语句(不用加where)
@pageSize int, --每页多少条记录
@index int = 1 , --指定当前为第几页
@total int out
AS
BEGIN
set nocount on
--表名,视图---不接受sql语句
declare @sql nvarchar(4000)
set @sql = 'select @total=count(id) from ' + @table +' where 1=1' + @where
exec sp_executesql @sql,N'@total int output',@total output--计算总记录数
print @total
if (@total is null)
begin
set @total = 0
end
if (@order is null or @order='')
begin
set @order = 'order by id'
end
set @sql = 'select * from (select '+ @fields+', row_number() over ('+ @order +') AS rownum from '
set @sql = @sql + @table + ' WHERE 1 = 1' + @where + ') AS a '
set @sql = @sql + 'WHERE rownum>'+ cast(@pageSize*(@index-1) as varchar(3))+' AND rownum<= '+ cast(@pageSize*(@index) as varchar(3))
print @sql
exec (@sql)
set nocount off
END
GO
-- Add the parameters for the stored procedure here
@table varchar(50),
@fields varchar(5000) = '*', --字段名(全部字段为*)
@order varchar(5000), --排序字段(必须!支持多字段)
@where varchar(5000) = Null,--条件语句(不用加where)
@pageSize int, --每页多少条记录
@index int = 1 , --指定当前为第几页
@total int out
AS
BEGIN
set nocount on
--表名,视图---不接受sql语句
declare @sql nvarchar(4000)
set @sql = 'select @total=count(id) from ' + @table +' where 1=1' + @where
exec sp_executesql @sql,N'@total int output',@total output--计算总记录数
print @total
if (@total is null)
begin
set @total = 0
end
if (@order is null or @order='')
begin
set @order = 'order by id'
end
set @sql = 'select * from (select '+ @fields+', row_number() over ('+ @order +') AS rownum from '
set @sql = @sql + @table + ' WHERE 1 = 1' + @where + ') AS a '
set @sql = @sql + 'WHERE rownum>'+ cast(@pageSize*(@index-1) as varchar(3))+' AND rownum<= '+ cast(@pageSize*(@index) as varchar(3))
print @sql
exec (@sql)
set nocount off
END
GO