/**//*
******************************************************************************************
??過程名稱：Common_SoBiGo_Percent_Pagination
??過程功能：
??代碼設(shè)計：小朱(zsy619@163.com)
??設(shè)計時間：2005-11-3?13:58:26
******************************************************************************************
??功能描述：

******************************************************************************************
??如果您修改了我的程序，請留下修改記錄，以便對程序進行維護，謝謝??！！！
==========================================================================================
??修改人????????????修改時間????????????????修改原因
------------------------------------------------------------------------------------------

==========================================================================================

******************************************************************************************
??備注：
*****************************************************************************************
*/
Create?Procedure?[dbo].[Common_SoBiGo_Percent_Pagination]
????@tblName???varchar(255)='T_Category',?????????????--?表名
????@strGetFields?varchar(1000)='*',???????--?需要返回的列?
????@fldName?varchar(255)='',??????????--?排序的字段名
????@PageSize???int=10,?????????????????--?頁尺寸
????@PageIndex??int=1,???????????????--?頁碼
????@doCount??bit=1,???????????--?返回記錄總數(shù),?非?0?值則返回
????@orderType?bit=0,?????????--?設(shè)置排序類型,?非?0?值則降序
????@strWhere??varchar(1500)=''??--?查詢條件?(注意:?不要加?where)
AS
????Declare?@intResult?Int
????Begin?Tran
????-----------------------------------------------------------------代碼設(shè)計--------------------------------------------------------------------
????declare?@strSQL???varchar(5000)???????--?主語句
????declare?@strTmp???varchar(110)????????--?臨時變量
????declare?@strOrder?varchar(400)????????--?排序類型
????--set?@tblName='price'
????--set?@StrGetFields='*'--'m_price'+','+'I_merchandiseID'
????--set?@fldname='D_Updatetime'
????--set?@pagesize=1
????--set?@pageIndex=1
????--set?@docount=0
????--set?@orderType=1
????--set?@strWhere='I_MerchandiseID>10'
????if?@doCount?!=?0
????begin
????????if?@strWhere?!=''
????????????set?@strSQL?=?'select?count(*)?as?Total??from?['?+?@tblName?+?']?where?'+@strWhere+''
????????else
????????????set?@strSQL?=?'select?count(*)?as?Total?from?['?+?@tblName?+?']'
????end??
????--以上代碼的意思是如果@doCount傳遞過來的不是0，就執(zhí)行總數(shù)統(tǒng)計。以下的所有代碼都是@doCount為0的情況
????else
????begin
????????if?@orderType?!=?0
????????begin
????????????set?@strTmp?=?'<(select?min'
????????????set?@strOrder?=?'?order?by?['?+?@fldName?+']?desc'
????????????--如果@OrderType不是0，就執(zhí)行降序，這句很重要！
????????end
????????else
????????begin
????????????set?@strTmp?=?'>(select?max'
????????????set?@strOrder?=?'?order?by?['?+?@fldName?+']?asc'
????????end
????
????????if?@PageIndex?=?1
????????begin
????????????if?@strWhere?!=?''???
????????????????set?@strSQL?=?'select?top?'?+?str(@PageSize)?+'?'+@strGetFields+?'??from?['?+?@tblName?+?']?where?'?+?@strWhere?+?'?'?+?@strOrder
????????????else
????????????????set?@strSQL?=?'select?top?'?+?str(@PageSize)?+'?'+@strGetFields+?'??from?['+?@tblName?+?']?'+?@strOrder
????????--如果是第一頁就執(zhí)行以上代碼，這樣會加快執(zhí)行速度
????????end
????????else
????????begin
????????--以下代碼賦予了@strSQL以真正執(zhí)行的SQL代碼
????????????set?@strSQL?=?'select?top?'?+?str(@PageSize)?+'?'+@strGetFields+?'??from?['
????????????????+?@tblName?+?']?where?['?+?@fldName?+?']'?+?@strTmp?+?'(['+?@fldName?+?'])?from?(select?top?'?+?str((@PageIndex-1)*@PageSize)?+?'?['+?@fldName?+?']?from?['?+?@tblName?+?']'?+?@strOrder?+?')?as?tblTmp)'+?@strOrder
????????????if?@strWhere?!=?''
????????????????set?@strSQL?=?'select?top?'?+?str(@PageSize)?+'?'+@strGetFields+?'??from?['
????????????????????+?@tblName?+?']?where?['?+?@fldName?+?']'?+?@strTmp?+?'(['
????????????????????+?@fldName?+?'])?from?(select?top?'?+?str((@PageIndex-1)*@PageSize)?+?'?['
????????????????????+?@fldName?+?']?from?['?+?@tblName?+?']?where?'?+?@strWhere?+?'?'
????????????????????+?@strOrder?+?')?as?tblTmp)?and?'?+?@strWhere?+?'?'?+?@strOrder
????????end?
????end???
????exec(@strSQL)
????Set?@intResult?=?@@ROWCOUNT
????----------------------------------------------------------------------------------------------------------------------------------------------------
????If?@@Error?<>?0
????Begin
????????RollBack?Tran
????????Return?-1
????End
????Else
????Begin
????????Commit?Tran
????????Return?@intResult
????End
GO

總結(jié)

以上是生活随笔為你收集整理的数据库分页存储过程（4）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。