(
@PageSize int , -- 每页的记录条数
@PageNumber int , -- 当前页面
@QuerySql varchar ( 1000 ), -- 部分查询字符串,如:"* From Table order by id desc"
@KeyField varchar ( 500 )
)
AS
Begin
Declare @SqlTable AS varchar ( 1000 )
Declare @SqlText AS Varchar ( 1000 )
Set @SqlTable = ' Select Top ' + CAST ( @PageNumber * @PageSize AS varchar ( 30 )) + ' ' + @QuerySql
Set @SqlText = ' Select Top ' + Cast ( @PageSize AS varchar ( 30 )) + ' * From '
+ ' ( ' + @SqlTable + ' ) As TembTbA '
+ ' Where ' + @KeyField + ' Not In (Select Top ' + CAST (( @PageNumber - 1 ) * @PageSize AS varchar ( 30 )) + ' ' + @KeyField + ' From '
+ ' ( ' + @SqlTable + ' ) AS TempTbB) '
Exec ( @SqlText )
End
GO
调用代码:
myComm = New SqlClient.SqlCommand("SP_GetRecordByPage", myConn)
myComm.CommandType = CommandType.StoredProcedure
myComm.Parameters.Add(New SqlClient.SqlParameter("@queryStr", SqlDbType.NVarChar, 1000))
myComm.Parameters("@queryStr").Value = " * from table order by id DESC"
myComm.Parameters.Add(New SqlClient.SqlParameter("@keyField", SqlDbType.NVarChar, 200))
myComm.Parameters("@keyField").Value = "[id]"
myComm.Parameters.Add(New SqlClient.SqlParameter("@pageSize", SqlDbType.NVarChar, 1000))
myComm.Parameters("@pageSize").Value = PageSize
myComm.Parameters.Add(New SqlClient.SqlParameter("@pageNumber", SqlDbType.NVarChar, 1000))
myComm.Parameters("@pageNumber").Value = myPage - 1