SqlServer通用存储过程的编写

通用存储过程的编写

对数据库的操作基本上就四种: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,

      @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


分析:

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

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

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

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

posted on 2004-09-16 10:01  余波  阅读(5146)  评论(8编辑  收藏  举报

导航