/****** Object:  StoredProcedure [dbo].[spSqlPageByRownumber]    Script Date: 2015/3/5 17:34:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[spSqlPageByRownumber]
@tbName varchar(255),        --表名
@tbFields varchar(1000),      --返回字段
@PageSize int,                --页尺寸
@PageIndex int,                --页码
@strWhere varchar(1000),    --查询条件
@StrOrder varchar(255)  --排序条件
as
declare @strSql varchar(5000)    --主语句
declare @strSqlCount nvarchar(500)--
--------------总记录数---------------
if @strWhere !=''
begin
set @strSqlCount='Select count(*) as TotalCout from  ' + @tbName + ' where '+ @strWhere
end
else
begin
set @strSqlCount='Select count(*) as TotalCout from  ' + @tbName
end
--------------分页------------
if @PageIndex <= 0
begin
  set @PageIndex = 1
end
if @strWhere !=''
begin
set @strSql='Select * from (Select  row_number() over('+@strOrder+') rowId,'+ @tbFields
+' from ' + @tbName + ' where  ' + @strWhere+' ) tb where tb.rowId >'+str((@PageIndex-1)*@PageSize)
+' and tb.rowId <= ' +str(@PageIndex*@PageSize)
end
else
begin
set @strSql='Select * from (Select  row_number() over('+@strOrder+') rowId,'+ @tbFields
+' from ' + @tbName + ' ) tb where tb.rowId >'+str((@PageIndex-1)*@PageSize)
+' and tb.rowId <= ' +str(@PageIndex*@PageSize)
end

exec(@strSqlCount)
exec(@strSql)
GO

exec [dbo].spSqlPageByRownumber 'GoodsCategory','*',10,2,'Cid > 0','order by Cid desc'