SqlServer通用存储过程的编写

通用存储过程的编写

对数据库的操作基本上就四种：Insert、Update、Delete和Select，而Update和Insert两种操作又可以作简单的合并，这样下来，基本上一个数据表对应三个存储过程便可以完成绝大多数的数据库操作。存储过程命名规则：Operate_TableName。比如表Order_Info对应三个存储过程：AddEdit_Order_Info、Delete_Order_Info、Search_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,

      @OutID INT = 0 OUTPUT

)

AS

DECLARE @strSQL NVARCHAR(1000)

 

IF @ReturnCount<>-1

     BEGIN

     SET @strSQL = 'SELECT  TOP  '+@ReturnCount+' * FROM [Order_Info] WHERE @tmpOrderID = @tmpOrderID '

     END

ELSE

     BEGIN

     SET @strSQL = 'SELECT * FROM [Order_Info] WHERE @tmpOrderID = @tmpOrderID '

     END

 

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

分析：

１、              三个存储过程的入参基本上相同，只有Search_Order_Info多了一个＠ReturnCount用来控制搜索信息的条数的。入参很有特点：与数据表字段的扩展对应。扩展方式有三种：数字型和日期型扩展出“极小”和“极大”两个属性，例如数字型的OrderStateID对应的参数有三个@OrderStateID、@OrderStateID_Min 、@OrderStateID_Max ，时间型的AddTime对应@AddTime、@AddTime_Rof、@AddTime_Eof ；如果是字符型的，则会扩展出一个用来进行模糊搜索的属性，例如Title对应@Title、@Title_Like。之所以这样设计，是为了组合出更具适应性的条件语句。三个存储过程都有一个出参，就是表的唯一标识ID。这个主要在“添加和更新”操作中使用。当然搜索的时候也可以当唯一键返回。这个唯一标识ID也是来判断是Insert或Update的标识。

２、              入参都有赋初值，然后动态构建Sql语句的时候，会判断各入参是否等于初值，如果不等于表示是外面传进来的传，便参与Sql语句的构建。这种灵活性是程序适应性的保证。这样，我们就可以在程序员通过控制是否给入参传值来判断是否要进行某一栏位进行更新或是否要把某一栏位的信息参与条件语句的构成。

３、              用系统存储过程sp_executesql来执行Sql语句，完全数据库操作。用系统存储过程来执行sql语句有一个好处，就是可以实现特殊字符的自动转义。

４、              三个存储过程都有统一的构建规律，所以可以使用自动化工具依据表结构直接生成。