create
PROCEDURE
[dbo].[GetPageDataOutRowNumber]
(
@tn nvarchar(30),
--表名称
@idn nvarchar(20),
--表主键名称
@pi
int
= 1,
--当前页数
@ps
int
= 7,
--每页大小
@wh nvarchar(255) =
''
,
--wehre查询条件
@oby nvarchar(255) =
''
,
--orderby 排序
@rc
int
output
,
--总行数(传出参数)
@pc
int
output
--总页数(传出参数)
)
AS
DECLARE
@sql NVARCHAR(225)=
''
,@sqlCount NVARCHAR(225)=
''
--1.计算总行数和总页数
SET
@sqlCount =
'SELECT @rc=COUNT(['
+@idn+
']),@pc=CEILING((COUNT('
+@idn+
')+0.0)/'
+
CAST
(@ps
AS
VARCHAR
)+
') FROM '
+ @tn
IF LEN(@wh)>1
set
@sqlCount=@sqlCount+
' WHERE '
+@wh
print @sqlCount
EXEC
SP_EXECUTESQL @sqlCount,N
'@rc INT OUTPUT,@pc INT OUTPUT'
,@rc
OUTPUT
,@pc
OUTPUT
--2.分页
--2.1如果是第一页，则直接查询
IF @pi = 1
BEGIN
SET
@sql=
'SELECT TOP '
+str(@ps) +
' * FROM '
+@tn
IF LEN(@wh)>1
set
@sql=@sql+
' WHERE '
+@wh
IF LEN(@oby)>1
SET
@sql=@sql+
' order by '
+@oby
EXEC
(@sql)
END
ELSE
--2.2如果不是第一页，则拼接查询语句
BEGIN
SET
NOCOUNT
ON
SET
@sql=
'SELECT * FROM (select row_number() over(order by '
IF LEN(@oby)>1
set
@sql=@sql + @oby+
') as rowNum,* from '
+@tn
else
set
@sql=@sql + @idn+
') as rowNum,* from '
+@tn
IF LEN(@wh)>1
set
@sql=@sql+
' where '
+@wh
set
@sql=@sql+
')as temp where rowNum>'
+str(@ps * (@pi-1))+
' and rowNum<='
+str(@ps*@pi)
print @sql
EXEC
(@sql)
SET
NOCOUNT
OFF
END
--测试语句
declare
@rc
int
,@pc
int
exec
[GetPageDataOutRowNumber]
'Ams_Area'
,
'ar_id'
,2,5,
''
,
' ar_id desc'
,@rc
output
,@pc
output
select
@rc,@pc
