通用存储过程的编写

存储过程

通用存储过程的编写

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

分析:

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

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

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

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

时间: 2024-09-11 23:58:37

通用存储过程的编写的相关文章

生成交叉表的简单通用存储过程

存储过程 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_qry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[p_qry]GO /*--生成交叉表的简单通用存储过程  根据指定的表名,纵横字段,统计字段,自动生成交叉表 并可根据需要生成纵横两个方向的合计  注意,横向字段数目如果大于纵向字段数目,将自动交换纵横

Stored Procedure(存储过程)编写经验和优化措施

存储过程|优化 Stored Procedure(存储过程)编写经验和优化措施 一.前言:在经过一段时间的存储过程开发之后,写下了一些开发时候的小结和经验与大家共享,希望对大家有益,主要是针对Sybase和SQL Server数据库,但其它数据库应该有一些共性. 二.适合读者对象:数据库开发程序员,数据库的数据量很多,涉及到对SP(存储过程)的优化的项目开发人员,对数据库有浓厚兴趣的人. 三.介绍:在数据库的开发过程中,经常会遇到复杂的业务逻辑和对数据库的操作,这个时候就会用SP来封装数据库操作

复制表结构的通用存储过程

复制表结构的通用存储过程 --Transfer对象的重要属性 --1.属性 属性名 类型 描述 ------------------------------------------------------------------------ CopyAllDefaults Boolean 所有默认值 CopyAllObjects Boolean 所有对象 CopyAllRules Boolean 所有规则 CopyAllStoredProcedures Boolean 所有存储过程 CopyAl

Sql 导入 EXCEL 通用存储过程

Sql 导入 EXCEL 通用存储过程/* --下面两句在创建存储过程时必须先执行,否则不能创建本存储过程,或者调用出错 SET ANSI_WARNINGS ON--返回警告 GO SET ANSI_NULLS ON-- 指定在对空值使用等于   (=)   和不等于   (<>)   比较运算符时,这些运算符的   SQL-92   遵从行为. GO --将Excel数据导入到sql数据库 --版权 : 启程 letwego.cn --调用实例 EXEC spExcelOutIn  @str

关于存储过程的编写的一些体会_MsSql

由于周牛的JS知识很多,下面我来分享一下SQL下编写储存过程的初级阶段 在数据库界,在SQL,DB,甲骨文三大数据库中,通过编写储存过程增强或改变业务的功能,是一个手段,对数据库的灵活性的增强不言而喻.下面我来介绍下存储过程的编写 数据库存储过程的实质就是部署在数据库端的一组定义代码以及SQL: 我们来看看我们的分页储存实例: 例如: 表car的内容如下 编号 书名 价格 001 BMW $30000 002 BENZ $31000 实例1:查询表car 的内容的存储过程 复制代码 代码如下:

一步一步写算法(之通用算法的编写)

原文:一步一步写算法(之通用算法的编写) [ 声明:版权所有,欢迎转载,请勿用于商业用途.  联系信箱:feixiaoxing @163.com]     前面我们写过各种各样的算法,什么排序.查找.二叉树.队列.堆栈等等.但是我们在编写这些代码的时候却都有一个缺点,不知道大家发现了没有?那就是这些算法中使用的数据结构都是简单的int数据.所以,如果排序的是int,那么用起来没有什么问题.关键就是万一是其他的数据类型,那我们应该怎么办呢?     在c++中,有一种解决的方法.那就是类函数.就拿

SQL Server 分页查询通用存储过程(只做分页查询用)_MsSql

自开始做项目以来,一直在用.这段存储过程的的原创者(SORRY,忘记名字了),写得这段SQL代码很不错,我在这个基础上,按照我的习惯以及思维方式,调整了代码,只做分页查询用. /*---------------------------------------------- *procedure name : P_PageResult * author :Fay * create date : 2014-07-18 */ CREATE PROCEDURE prcPageResult -- 获得某一

SQL Server 分页查询通用存储过程(只做分页查询用)

自开始做项目以来,一直在用.这段存储过程的的原创者(SORRY,忘记名字了),写得这段SQL代码很不错,我在这个基础上,按照我的习惯以及思维方式,调整了代码,只做分页查询用. /*---------------------------------------------- *procedure name : P_PageResult * author :Fay * create date : 2014-07-18 */ CREATE PROCEDURE prcPageResult -- 获得某一

模糊查询的通用存储过程_MsSql

复制代码 代码如下: IF Exists(Select 1 From sysobjects Where Name='sp_search' And xType='P') Drop Procedure sp_search go /* 模糊查询的通用存储过程 create by sxm,date 2009-7-14 参数: @table_name 表名 @condition 条件 */ create proc sp_search(@table_name varchar(200),@condition