SQL SERVER 分页查询存储过程
明月清风精进不止 2020-01-14
/****** Object: StoredProcedure [dbo].[usp_selectbypage] Script Date: 01/14/2020 07:51:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_selectbypage]
@tablename varchar(10),
@columnname varchar(10),
@page varchar(5), ---页数
@pagecount varchar(5), --每页行数
@recordcount int output,--总的记录的条数
@pageamount int output --总页数
as
begin
declare @strsql nvarchar(255)
set @strsql = ‘select * from (
select ROW_NUMBER() over (order by ‘ + @columnname + ‘ desc) as rowid,* from ‘ + @tablename + ‘) t
where t.rowid >=((‘+‘-1)*‘+‘+1) and t.rowid <= (‘+‘*‘+‘)‘
exec (@strsql) -----不是有效的标识符 sql,exec执行时必须加（）
set @strsql = ‘select @count=count(*) from ‘ + @tablename
exec sp_executesql @strsql,N‘@count int output‘,@recordcount output
select @pageamount = CEILING(@recordcount*1.0/@pagecount)
end
