SQL 2000 2005通用儲存過程
SQL2000通用儲存過程(適用高版本):
Code
CREATE PROC P_viewPage
/**//**//**//*
nzperfect [no_mIss] 高效通用分頁儲存過程(雙向檢索) 2007.5.7 QQ:34813284
敬告：適用於單一主鍵或存在唯一值列的表或檢視
ps:Sql語句為8000位元組,呼叫時請注意傳入引數及sql總長度不要超過指定範圍
*/
@TableName VARCHAR(200), --表名
@FieldList VARCHAR(2000), --顯示列名，如果是全部欄位則為*
@PrimaryKey VARCHAR(100), --單一主鍵或唯一值鍵
@Where VARCHAR(2000), --查詢條件 不含'where'字元，如id>10 and len(userid)>9
@Order VARCHAR(1000), --排序 不含'order by'字元，如id asc,userid desc，必須指定asc或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(@TotalCount,'') = '' SET @TotalCount = 0
SET @Order = RTRIM(LTRIM(@Order))
SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))
SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')
WHILE CHARINDEX(', ',@Order) > 0 OR CHARINDEX(' ,',@Order) > 0
BEGIN
SET @Order = REPLACE(@Order,', ',',')
SET @Order = REPLACE(@Order,' ,',',')
END
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
PRINT('ERR_00')
RETURN
END
IF @SortType = 3
BEGIN
IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')
BEGIN PRINT('ERR_02') RETURN END
END
DECLARE @new_where1 VARCHAR(1000)
DECLARE @new_where2 VARCHAR(1000)
DECLARE @new_order1 VARCHAR(1000)
DECLARE @new_order2 VARCHAR(1000)
DECLARE @new_order3 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
BEGIN
SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' ASC'
SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' DESC'
END
IF @SortType = 2
BEGIN
SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' DESC'
SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' ASC'
END
END
ELSE
BEGIN
SET @new_order1 = ' ORDER BY ' + @Order
END
IF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0
BEGIN
SET @new_order1 = ' ORDER BY ' + @Order
SET @new_order2 = @Order + ','
SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')
SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')
SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)
IF @FieldList <> '*'
BEGIN
SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')
SET @FieldList = ',' + @FieldList
WHILE CHARINDEX(',',@new_order3)>0
BEGIN
IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0
BEGIN
SET @FieldList =
@FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))
END
SET @new_order3 =
SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))
END
SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))
END
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 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
IF @PageIndex = 1 --返回第一頁資料
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where1 + @new_order1
END
IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最後一頁資料
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
+ 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))
+ ' ' + @FieldList + ' FROM '
+ @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
+ @new_order1
END
END
ELSE
BEGIN
IF @SortType = 1 --僅主鍵正序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向檢索
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_order1 +' ) AS TMP) '+ @new_order1
END
ELSE --反向檢索
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
+ 'SELECT TOP ' + STR(@PageSize) + ' '
+ @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' < '
+ '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '
+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
SQL2005通用儲存過程:
Code
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:
-- Notes:
-- =============================================
ALTER Procedure [dbo].[up_Page2005]
@TableName varchar(500), --表名
@Fields varchar(8000) = '*', --欄位名(全部欄位為*)
@OrderField varchar(8000), --排序欄位(必須!支援多欄位)
@sqlWhere varchar(8000) = Null,--條件語句(不用加where)
@pageSize int, --每頁多少條記錄
@pageIndex int = 1 , --指定當前為第幾頁
@TotalPage int output --返回總頁數
as
begin
Begin Tran --開始事務
Declare @sql nvarchar(4000);
Declare @totalRecord int;
--計算總記錄數
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--計算總記錄數
--計算總頁數
select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
else
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere
--處理頁數超出範圍情況
if @PageIndex<=0
Set @pageIndex = 1
if @pageIndex>@TotalPage
Set @pageIndex = @TotalPage
--處理開始點和結束點
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@pageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1
--繼續合成sql語句
set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
--print @sql ;
Exec(@Sql)
---------------------------------------------------
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return @totalRecord ---返回記錄總數
End
end
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-557807/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 支援DISTINCT的通用分頁儲存過程（SQL2005）儲存過程SQL
- SQLServer 2005通用分頁儲存過程SQLServer儲存過程
- SQL SERVER 2005分頁儲存過程SQLServer儲存過程
- 【stswordman】SQL Server 2005: 儲存過程簽名SQLServer儲存過程
- 通用分頁儲存過程儲存過程
- SQL Server 2005的複製儲存過程選項BYSQLServer儲存過程
- SQL儲存過程示例SQL儲存過程
- SQL Server 儲存過程SQLServer儲存過程
- 【SQL Server】--儲存過程SQLServer儲存過程
- SQL Server 2000 的分頁查詢(儲存過程)SQLServer儲存過程
- 由Delphi程式改寫Sql(2000)的儲存過程SQL儲存過程
- SQL 儲存過程裡呼叫另一個儲存過程SQL儲存過程
- 解密SQLServer2005儲存過程解密SQLServer儲存過程
- 用XML和SQL 2000來管理儲存過程呼叫(轉)XMLSQL儲存過程
- SQL 分頁儲存過程SQL儲存過程
- MS SQL Server儲存過程SQLServer儲存過程
- Sql Server系列：儲存過程SQLServer儲存過程
- SQL 建立儲存過程PROCEDURESQL儲存過程
- 解密SQL SERVER儲存過程解密SQLServer儲存過程
- SQL儲存過程迴圈SQL儲存過程
- sql儲存過程分頁SQL儲存過程
- 通用的SQL Server資料庫查詢分頁儲存過程SQLServer資料庫儲存過程
- SQL2000分頁儲存過程，針對表,2005有自帶的row_numberSQL儲存過程
- Q&A：在SQL Server 2005中編寫儲存過程RVSQLServer儲存過程
- Sql server 2005遷移至Oracle系列之二：生成儲存過程SQLServerOracle儲存過程
- Excel資料匯入SQL Server2000的儲存過程ExcelSQLServer儲存過程
- 兩種SQL分頁方法儲存過程和遊標儲存過程SQL儲存過程
- Sql儲存過程分頁--臨時表儲存SQL儲存過程
- sql 儲存過程命名規範SQL儲存過程
- Oracle Pl/SQL 之 儲存過程OracleSQL儲存過程
- PL/SQL 05 儲存過程 procedureSQL儲存過程
- SQL Server基礎：儲存過程SQLServer儲存過程
- sql server儲存過程語法SQLServer儲存過程
- SQL總結（五）儲存過程SQL儲存過程
- SQL server儲存過程函式SQLServer儲存過程函式
- 拋磚引玉——通用分頁儲存過程儲存過程
- 生成sql server2000物件建立指令碼的儲存過程(轉)SQLServer物件指令碼儲存過程
- 使用SQL Server 2005的新函式構造分頁儲存過程SQLServer函式儲存過程