第一個支持唯一主鍵，第二支持多主鍵，測試過，效率一般
CREATE PROC P_viewPage
??? /*
??????? no_mIss 分頁存儲過程 2007.2.20? QQ:34813284
??????? 適用于單一主鍵或存在唯一值列的表或視圖?????????
??? */
??? @TableName VARCHAR(200),???? --表名
??? @FieldList VARCHAR(2000),??? --顯示列名
??? @PrimaryKey VARCHAR(100),??? --單一主鍵或唯一值鍵
??? @Where VARCHAR(1000),??????? --查詢條件 不含'where'字符
??? @Order VARCHAR(1000),??????? --排序 不含'order by'字符，如id asc,userid desc，當@SortType=3時生效
??? @SortType INT,?????????????? --排序規則 1:正序asc 2:倒序desc 3:多列排序
??? @RecorderCount INT,????????? --記錄總數 0:會返回總記錄
??? @PageSize INT,?????????????? --每頁輸出的記錄數
??? @PageIndex INT,????????????? --當前頁數
??? @TotalCount INT OUTPUT,????? --返回記錄總數
??? @TotalPageCount INT OUTPUT?? --返回總頁數
AS
??? SET NOCOUNT ON
??? IF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = ''
??????? OR ISNULL(@PrimaryKey,'') = ''
??????? OR @SortType < 1 OR @SortType >3
??????? OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0
??? BEGIN???????
??????? RETURN
??? END
?
??? DECLARE @new_where1 VARCHAR(1000)
??? DECLARE @new_where2 VARCHAR(1000)
??? DECLARE @new_order VARCHAR(1000)??
??? DECLARE @Sql VARCHAR(8000)
??? DECLARE @SqlCount NVARCHAR(4000)
??? IF ISNULL(@where,'') = ''
??????? BEGIN
??????????? SET @new_where1 = ' '
??????????? SET @new_where2 = ' WHERE? '
??????? END
??? ELSE
??????? BEGIN
??????????? SET @new_where1 = ' WHERE ' + @where
??????????? SET @new_where2 = ' WHERE ' + @where + ' AND '
??????? END
??? IF ISNULL(@order,'') = '' OR @SortType = 1? OR @SortType = 2
??????? BEGIN
??????????? IF @SortType = 1 SET @new_order = ' ORDER BY ' + @PrimaryKey + ' ASC'
??????????? IF @SortType = 2 SET @new_order = ' ORDER BY ' + @PrimaryKey + ' DESC'
??????? END
??? ELSE
??????? BEGIN
??????????? SET @new_order = ' ORDER BY ' + @Order
??????? END
??? SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
??????????????????? + CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1
???
??? IF @RecorderCount = 0
??????? BEGIN
???????????? EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',
?????????????????????????????? @TotalCount OUTPUT,@TotalPageCount OUTPUT
??????? END
??? ELSE
??????? BEGIN
???????????? SELECT @TotalCount = @RecorderCount???????????
??????? END
??? IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)
??????? BEGIN
??????????? SET @PageIndex =? CEILING((@TotalCount+0.0)/@PageSize)
??????? END
??? IF @PageIndex = 1
??????? BEGIN
??????????? SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
?????????????????????? + @TableName + @new_where1 + @new_order
??????? END
??? ELSE
??????? BEGIN
??????????? IF @SortType = 1
??????????????? BEGIN
??????????????????? SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
?????????????????????????????? + @TableName + @new_where2 + @PrimaryKey + ' > '
?????????????????????????????? + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '
?????????????????????????????? + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
?????????????????????????????? + ' FROM ' + @TableName
?????????????????????????????? + @new_where1 + @new_order +' ) AS TMP) '+ @new_order
??????????????? END
??????????? IF @SortType = 2
??????????????? BEGIN
??????????????????? SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
?????????????????????????????? + @TableName + @new_where2 + @PrimaryKey + ' < '
?????????????????????????????? + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '
?????????????????????????????? + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
?????????????????????????????? +' FROM '+ @TableName
?????????????????????????????? + @new_where1 + @new_order + ') AS TMP) '+ @new_order??????????????????????????????
??????????????? END??????
??????????? IF @SortType = 3
??????????????? BEGIN
??????????????????? IF CHARINDEX(',',@Order) = 0 BEGIN RETURN END
??????????????????? SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
?????????????????????????????? + @TableName + @new_where2 + @PrimaryKey + ' NOT IN (SELECT TOP '
?????????????????????????????? + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
?????????????????????????????? + ' FROM ' + @TableName + @new_where1 + @new_order + ')'
?????????????????????????????? + @new_order
??????????????? END
??????? END
??? EXEC(@Sql)
GO
?
CREATE PROC P_public_ViewPage_per
??? /*
??????? no_mIss 通用分頁存儲過程 2007.3.1? QQ:34813284
??????? 適用于聯合主鍵/單主鍵/存在能確定唯一行列/存在能確定唯一行的多列 (用英文,隔開)
??????? 調用：
??????????? 第一頁查詢時返回總記錄和總頁數及第一頁記錄：
??????????? EXECUTE P_public_ViewPage_per 'TableName','col1,col2,col3,col4','pk1,pk2,pk3',
??????????????? 'col5>0 and col7<9','pk1 asc,pk2 asc,pk3 asc',0,10,1,
??????????????? @TotalCount OUTPUT,@TotalPageCount OUTPUT
??????????? 其它頁調用，比如第89頁(假設第一頁查詢時返回總記錄為2000000)：
??????????? EXECUTE P_public_ViewPage_per 'TableName','col1,col2,col3,col4','pk1,pk2,pk3',
??????????????? 'col5>0 and col7<9','pk1 asc,pk2 asc,pk3 asc',2000000,10,89,
??????????????? @TotalCount OUTPUT,@TotalPageCount OUTPUT
??? */
??? @TableName VARCHAR(200),???? --表名
??? @FieldList VARCHAR(2000),??? --顯示列名
??? @PrimaryKey VARCHAR(100),??? --單一主鍵或唯一值鍵或聯合主鍵列表(用英文,隔開)或能確定唯一行的多列列表(用英文,隔開)
??? @Where VARCHAR(1000),??????? --查詢條件 不含'where'字符
??? @Order VARCHAR(1000),??????? --排序 不含'order by'字符,用英文,隔開?
??? @RecorderCount INT,????????? --記錄總數 0:會返回總記錄
??? @PageSize INT,?????????????? --每頁輸出的記錄數
??? @PageIndex INT,????????????? --當前頁數
??? @TotalCount INT OUTPUT,????? --返回記錄總數
??? @TotalPageCount INT OUTPUT?? --返回總頁數
AS
??? SET NOCOUNT ON
???
??? SET @FieldList = REPLACE(@FieldList,' ','')
??? IF @FieldList = '*'
??????? BEGIN SET @FieldList = 'A.*'END
??? ELSE
??????? BEGIN
??????????? SET @FieldList = 'A.' + REPLACE(@FieldList,',',',A.')
??????? END
???
??? WHILE CHARINDEX(', ',@Order)>0
??? BEGIN
??????? SET @Order = REPLACE(@Order,', ',',')
??? END
??? IF ISNULL(@TableName,'') = '' OR ISNULL(@PrimaryKey,'') = ''???????
??????? OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0
??? BEGIN???????
??????? RETURN
??? END
?
??? DECLARE @new_where1 VARCHAR(1000)
??? DECLARE @new_where2 VARCHAR(1000)
??? DECLARE @new_where3 VARCHAR(1000)
??? DECLARE @new_where4 VARCHAR(1000)
??? DECLARE @new_order1 VARCHAR(1000)
??? DECLARE @new_order2 VARCHAR(1000)
??? DECLARE @Fields VARCHAR(1000)
??? DECLARE @Sql VARCHAR(8000)
??? DECLARE @SqlCount NVARCHAR(4000)
??? SET @Fields = @PrimaryKey + ','
??? SET @new_where2 = ''
??? SET @new_where4 = ''
??? IF ISNULL(@where,'') = ''
??????? BEGIN
??????????? SET @new_where1 = ' '
??????????? SET @new_where3 = ' WHERE '
??????? END
??? ELSE
??????? BEGIN???????????
??????????? SET @new_where1 = ' WHERE ' + @where + ' '
??????????? SET @new_where3 = ' WHERE 1=1 '
??????????????????? + REPLACE(' AND ' + @where,' AND ',' AND A.')+ ' AND '
??????? END
???
??? WHILE CHARINDEX(',',@Fields)>0
??? BEGIN
??????? SET @new_where2 = @new_where2
??????????? + 'A.' + LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1))
??????????? + ' = B.' + LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1)) + ' AND '
??????? SET @new_where4 = @new_where4
??????????? + 'B.' + LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1)) + ' IS NULL AND '
??????? SET @Fields = SUBSTRING(@Fields,CHARINDEX(',',@Fields)+1,LEN(@Fields))
??? END
??? SET @new_where2 = LEFT(@new_where2,LEN(@new_where2)-4)
??? SET @new_where4 = LEFT(@new_where4,LEN(@new_where4)-4)
??? IF ISNULL(@order,'') = ''
??????? BEGIN
??????????? SET @new_order1 = ''
??????????? SET @new_order2 = ''
??????? END
??? ELSE
??????? BEGIN
??????????? SET @new_order1 = ' ORDER BY ' + @Order
??????????? SET @new_order2 = ' ORDER BY '
??????????????????? + RIGHT(REPLACE(',' + @Order,',',', A.' ),
??????????????????????????? LEN(REPLACE(',' + @Order,',',', A.' ))-1)
??????? END
??? SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
??????????????????? + CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName
??????????????????? + ' A ' +? @new_where1
???
??? IF @RecorderCount = 0
??????? BEGIN
???????????? EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',
?????????????????????????????? @TotalCount OUTPUT,@TotalPageCount OUTPUT
??????? END
??? ELSE
??????? BEGIN
???????????? SELECT @TotalCount = @RecorderCount???????????
??????? END
??? IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)
??????? BEGIN
??????????? SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)
??????? END
??? IF @PageIndex = 1
??????? BEGIN
??????????? SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
?????????????????????? + @TableName + ' A'+ @new_where1 + @new_order1
??????? END
??? ELSE
??????? BEGIN
??????????? SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
?????????????????????? + @TableName + ' A LEFT JOIN (SELECT TOP '
?????????????????????? + STR(@PageSize*(@PageIndex-1))
?????????????????????? + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1
?????????????????????? + @new_order1 + ' )B ON ' + @new_where2 + @new_where3
?????????????????????? + @new_where4 + @new_order2
??????? END
EXEC(@Sql)
GO
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
