SQL存储过程分页

CREATE   Procedure  SP_GetRecordByPage

(

@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