第一：
ALTER procedure Consignment
@tablenamevarchar(80) ,
@strOrdervarchar(50) ,
@PageIndexint=1,
@PageSizeint=15,
@strGetFieldsvarchar(200) ='*',
@OutPutint output
as
Begin
Declare@strSqlvarchar(500)
DECLARE @SQL NVARCHAR(1000)
DECLARE @RBIGINT
SET @SQL= N'select @R=count(*) from '+@TableName
EXEC SP_EXECUTESQL @SQL, N' @R BIGINT OUTPUT', @R OUTPUT
SET @OutPut= @R
if(@PageIndex=1)
Begin
set@strSql='select top '+str(@PageSize)+''+@strGetFields+' from '+@tablename+' order by '+@strOrder
End
Else
set@strSql='select top '+str(@PageSize)+''+@strGetFields+' from '+@tablename+' where ('+@strOrder
+' >= ( select Max('+@strOrder+') from ( select top '+str(@PageSize*@PageIndex)+' * from '+@tablename+' order by '
+@strOrder+' ) as tempTable)) order by '+@strOrder
select@strSql
exec(@strSql)
End
第二：
alter
procedure
AllProce
@tablename
varchar
(
200
) ,
--
表名
@strGetFields
varchar
(
200
)
=
'
*
'
,
--
查询列名
@PageIndex
int
=
1
,
--
页码
@pageSize
int
=
15
,
--
页面大小
@strWhere
varchar
(
100
)
=
''
,
--
查询条件
@strOrder
varchar
(
100
)
=
''
,
--
排序列名
@intOrder
bit
=
0
,
--
排序类型 1为升序
@CountAll
bigint
output
--
返回纪录总数用于计算页面数
as
begin
declare
@strSql
varchar
(
500
)
--
主语句
declare
@strTemp
varchar
(
100
)
--
临时变量
declare
@strOrders
varchar
(
50
)
--
排序语句
declare
@table
varchar
(
70
)
declare
@SQL
nvarchar
(
1000
)
declare
@R
bigint
set
@SQL
=
N
'
select @R=count(*) from
'
+
convert
(
nvarchar
(
200
),
@TableName
)
exec
SP_EXECUTESQL
@SQL
, N
'
@R BIGINT OUTPUT
'
,
@R
OUTPUT
set
@CountAll
=
@R
if
@intOrder
=
0
begin
--
为0是升序
set
@strTemp
=
'
>(select max
'
set
@strOrders
=
'
order by
'
+
@strOrder
+
'
asc
'
end
else
begin
--
否则为降序
set
@strTemp
=
'
<(select min
'
set
@strOrders
=
'
order by
'
+
@strOrder
+
'
desc
'
end
if
@PageIndex
=
1
--
第一页直接读出纪录
begin
if
@strWhere
=
''
begin
set
@strSql
=
'
select top
'
+
str
(
@pageSize
)
+
'
'
+
@strGetFields
+
'
from
'
+
@tablename
+
'
'
+
@strOrders
end
else
begin
set
@strSql
=
'
select top
'
+
str
(
@pageSize
)
+
'
'
+
@strGetFields
+
'
from
'
+
@tablename
+
'
where
'
+
@strWhere
+
'
'
+
@strOrders
end
end
else
begin
set
@strSql
=
'
select top
'
+
str
(
@pageSize
)
+
'
'
+
@strGetFields
+
'
from
'
+
@tablename
+
'
where
'
+
@strOrder
+
'
'
+
@strTemp
+
'
(
'
+
@strOrder
+
'
)
'
+
'
from (select top
'
+
str
((
@pageIndex
-
1
)
*
@pageSize
)
+
'
'
+
@strGetFields
+
'
from
'
+
@tablename
+
'
'
+
@strOrders
+
'
) as tempTable )
'
+
@strOrders
if
@strWhere
!=
'
'
begin
set
@strSql
=
'
select top
'
+
str
(
@pageSize
)
+
'
'
+
@strGetFields
+
'
from
'
+
@tablename
+
'
where
'
+
@strOrder
+
'
'
+
@strTemp
+
'
(
'
+
@strOrder
+
'
)
'
+
'
from(select top
'
+
str
((
@pageIndex
-
1
)
*
@pageSize
)
+
'
'
+
@strGetFields
+
'
from
'
+
@tablename
+
'
where
'
+
@strWhere
+
'
'
+
@strOrders
+
'
) as tempTable) where
'
+
@strWhere
+
'
'
+
@strOrders
end
end
exec
(
@strSql
)
end
GO