USE [SaleDB]
GO
/****** Object: StoredProcedure [dbo].[spSqlPageByRowNumber] Script Date: 11/02/2015 22:55:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[spSqlPageByRowNumber]
@tbName varchar(1000), --表名
@tbFields varchar(2000), --返回字段
@PageSize int, --页尺寸
@PageIndex int, --页码
@strWhere varchar(1000), --查询条件
@StrOrder varchar(1000), --排序条件
@Total int output --返回总记录数
as
declare @strSql varchar(5000) --主语句
declare @strSqlCount nvarchar(1000)--查询记录总数主语句
--------------总记录数---------------
if @strWhere !=''
begin
set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName + ' where 1=1 '+ @strWhere
end
else
begin
set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName
end
--------------分页------------
if @PageIndex <= 0
begin
set @PageIndex = 1
end
set @strSql='Select * from (Select row_number() over('+@strOrder+') rowId,'+ @tbFields
+' from ' + @tbName + ' where 1=1 ' + @strWhere+' ) tb where tb.rowId >'+str((@PageIndex-1)*@PageSize)
+' and tb.rowId <= ' +str(@PageIndex*@PageSize)
exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output
exec(@strSql)
GO
