中国IT动力,最新最全的IT技术教程
最新100篇 | 推荐100篇 | 专题100篇 | 排行榜 | 搜索 | 在线API文档
首 页 | 程序开发 | 操作系统 | 软件应用 | 图形图象 | 网络应用 | 精文荟萃 | 教育认证 | 硬件维护 | 未整理篇 | 站长教程
ASP JS PHP工程 ASP.NET 网站建设 UML J2EESUN .NET VC VB VFP 网络维护 数据库 DB2 SQL2000 Oracle Mysql
服务器 Win2000 Office C DreamWeaver FireWorks Flash PhotoShop 上网宝典 CorelDraw 协议大全 网络安全 微软认证
硬件维护  CPU  主板  硬盘  内存  显卡  显示器  键盘鼠标  声卡音箱  打印机  机箱电源  BIOS  网卡  C#  Java  Delphi  vs.net2005
  当前位置:> 程序开发 > 数据库开发 > 数据库综合
通用存储过程的编写
作者:未知 时间:2004-09-16 12:12 出处:Blog 责编:chinaitpower
              摘要:暂无

通用存储过程的编写

对数据库的操作基本上就四种:InsertUpdateDeleteSelect,而UpdateInsert两种操作又可以作简单的合并,这样下来,基本上一个数据表对应三个存储过程便可以完成绝大多数的数据库操作。存储过程命名规则:Operate_TableName。比如表Order_Info对应三个存储过程:AddEdit_Order_InfoDelete_Order_InfoSearch_Order_Info,下面先列出相关代码,然后作总体分析。

一、AddEdit_Order_Info

 

/*************************************************************

**  Name      :    AddEdit_Order_Info

**  Creater        :    PPCoder Designed By PPCode Studio(PPTech.Net)

**  Create Date    :    2004-9-6 8:30:17

**  Modifer        :   Rexsp

**  Modify Date    :    2004-9-6 8:30:17

**  Description :  AddEdit information for Order_Info

**************************************************************/ 

ALTER PROCEDURE dbo.AddEdit_Order_Info

(

      @OrderStateID Int = -1,

      @OrderStateID_Min Int = -1,

      @OrderStateID_Max Int = -1,

      @OrderUserID Int = -1,

      @OrderUserID_Min Int = -1,

      @OrderUserID_Max Int = -1,

      @OrderID Int = -1,

      @OrderID_Min Int = -1,

      @OrderID_Max Int = -1,

      @ProductID Int = -1,

      @ProductID_Min Int = -1,

      @ProductID_Max Int = -1,

      @CustomizeID Int = -1,

      @CustomizeID_Min Int = -1,

      @CustomizeID_Max Int = -1,

      @OutID INT = 0 OUTPUT

)

AS

IF @OrderID=-1

     BEGIN

         INSERT INTO [Order_Info] (

                                          [OrderStateID],

                                          [OrderUserID],

                                          [ProductID],

                                          [CustomizeID]

                                     )

                                     VALUES(

                                          @OrderStateID,

                                          @OrderUserID,

                                          @ProductID,

                                          @CustomizeID

                                     )

         Set  @OutID = @@IDENTITY

     END

 

ELSE

 

     BEGIN

         DECLARE @strSQL NVARCHAR(1000)

         SET @strSQL = 'UPDATE [Order_Info] SET @tmpOrderID = @tmpOrderID'

         IF @OrderStateID <> -1

              BEGIN

                   SET @strSQL = @strSQL + ', [OrderStateID] = @tmpOrderStateID'

              END

         IF @OrderUserID <> -1

              BEGIN

                   SET @strSQL = @strSQL + ', [OrderUserID] = @tmpOrderUserID'

              END

         IF @ProductID <> -1

              BEGIN

                   SET @strSQL = @strSQL + ', [ProductID] = @tmpProductID'

              END

         IF @CustomizeID <> -1

              BEGIN

                   SET @strSQL = @strSQL + ', [CustomizeID] = @tmpCustomizeID'

              END

         SET @strSQL = @strSQL + ' WHERE [OrderID] = @tmpOrderID'

 

         BEGIN TRAN

         EXECUTE sp_executesql @strSQL, N'

 

                       @tmpOrderStateID INT,

                       @tmpOrderUserID INT,

                       @tmpOrderID INT,

                       @tmpProductID INT,

                       @tmpCustomizeID INT',

                       @tmpOrderStateID=@OrderStateID,

                       @tmpOrderUserID=@OrderUserID,

                       @tmpOrderID=@OrderID,

                       @tmpProductID=@ProductID,

                       @tmpCustomizeID=@CustomizeID

         Set  @OutID = @OrderID

 

         IF @@error!=0

              BEGIN

                   ROLLBACK

              END

         ELSE

              BEGIN

                   COMMIT

              END

     END

RETURN

 

二、Delete_Order_Info

 

/*************************************************************

**  Name      :    Delete_Order_Info

**  Creater        :    PPCoder Designed By PPCode Studio(PPTech.Net)

**  Create Date    :    2004-9-6 8:30:17

**  Modifer        :   Rexsp

**  Modify Date    :    2004-9-6 8:30:17

**  Description :  Delete information for Order_Info

**************************************************************/ 

ALTER PROCEDURE dbo.Delete_Order_Info

(

      @OrderStateID Int = -1,

      @OrderStateID_Min Int = -1,

      @OrderStateID_Max Int = -1,

      @OrderUserID Int = -1,

      @OrderUserID_Min Int = -1,

      @OrderUserID_Max Int = -1,

      @OrderID Int = -1,

      @OrderID_Min Int = -1,

      @OrderID_Max Int = -1,

      @ProductID Int = -1,

      @ProductID_Min Int = -1,

      @ProductID_Max Int = -1,

      @CustomizeID Int = -1,

      @CustomizeID_Min Int = -1,

      @CustomizeID_Max Int = -1,

      @OutID INT = 0 OUTPUT

)

AS

DECLARE @strSQL NVARCHAR(1000)

SET @strSQL = 'DELETE  FROM [Order_Info] WHERE @tmpOrderID = @tmpOrderID '

IF @OrderStateID<>-1

     BEGIN

         SET @strSQL = @strSQL + ' AND OrderStateID = @tmpOrderStateID'

     END

 

IF @OrderStateID_Min<>-1

     BEGIN

         SET @strSQL = @strSQL + ' AND OrderStateID_Min = @tmpOrderStateID_Min'

     END

 

IF @OrderStateID_Max<>-1

     BEGIN

         SET @strSQL = @strSQL + ' AND OrderStateID_Max = @tmpOrderStateID_Max'

     END

 

IF @OrderUserID<>-1

     BEGIN

         SET @strSQL = @strSQL + ' AND OrderUserID = @tmpOrderUserID'

     END

 

IF @OrderUserID_Min<>-1

     BEGIN

         SET @strSQL = @strSQL + ' AND OrderUserID_Min = @tmpOrderUserID_Min'

     END

 

IF @OrderUserID_Max<>-1

     BEGIN

         SET @strSQL = @strSQL + ' AND OrderUserID_Max = @tmpOrderUserID_Max'

     END

 

IF @OrderID<>-1

     BEGIN

         SET @strSQL = @strSQL + ' AND OrderID = @tmpOrderID'

     END

 

IF @OrderID_Min<>-1

     BEGIN

         SET @strSQL = @strSQL + ' AND OrderID_Min = @tmpOrderID_Min'

     END

 

IF @OrderID_Max<>-1

     BEGIN

         SET @strSQL = @strSQL + ' AND OrderID_Max = @tmpOrderID_Max'

     END

 

IF @ProductID<>-1

     BEGIN

         SET @strSQL = @strSQL + ' AND ProductID = @tmpProductID'

     END

 

IF @ProductID_Min<>-1

     BEGIN

         SET @strSQL = @strSQL + ' AND ProductID_Min = @tmpProductID_Min'

     END

 

IF @ProductID_Max<>-1

     BEGIN

         SET @strSQL = @strSQL + ' AND ProductID_Max = @tmpProductID_Max'

     END

 

IF @CustomizeID<>-1

     BEGIN

         SET @strSQL = @strSQL + ' AND CustomizeID = @tmpCustomizeID'

     END

 

IF @CustomizeID_Min<>-1

     BEGIN

         SET @strSQL = @strSQL + ' AND CustomizeID_Min = @tmpCustomizeID_Min'

     END

 

IF @CustomizeID_Max<>-1

     BEGIN

         SET @strSQL = @strSQL + ' AND CustomizeID_Max = @tmpCustomizeID_Max'

     END

 

         BEGIN TRAN

         EXECUTE sp_executesql @strSQL, N'

 

                       @tmpOrderStateID INT,

                       @tmpOrderUserID INT,

                       @tmpOrderID INT,

                       @tmpProductID INT,

                       @tmpCustomizeID INT',

                       @tmpOrderStateID=@OrderStateID,

                       @tmpOrderUserID=@OrderUserID,

                       @tmpOrderID=@OrderID,

                       @tmpProductID=@ProductID,

                       @tmpCustomizeID=@CustomizeID

         Set  @OutID = @OrderID

 

         IF @@error!=0

              BEGIN

                   ROLLBACK

              END

         ELSE

              BEGIN

                   COMMIT

              END

RETURN

 

三、 Search_Order_Info

 

 

/*************************************************************

**  Name      :    Search_Order_Info

**  Creater        :    PPCoder Designed By PPCode Studio(PPTech.Net)

**  Create Date    :    2004-9-6 8:30:17

**  Modifer        :   Rexsp

**  Modify Date    :    2004-9-6 8:30:17

**  Description :  Search information for Order_Info

**************************************************************/ 

ALTER PROCEDURE dbo.Search_Order_Info

(

      @OrderStateID Int = -1,

      @OrderStateID_Min Int = -1,

      @OrderStateID_Max Int = -1,

      @OrderUserID Int = -1,

      @OrderUserID_Min Int = -1,

      @OrderUserID_Max Int = -1,

      @OrderID Int = -1,

      @OrderID_Min Int = -1,

      @OrderID_Max Int = -1,

      @ProductID Int = -1,

      @ProductID_Min Int = -1,

      @ProductID_Max Int = -1,

      @CustomizeID Int = -1,

      @CustomizeID_Min Int = -1,

      @CustomizeID_Max Int = -1,

      @ReturnCount INT=-1,