IDC资讯中心
通用存储过程的编写
对数据库的操作基本上就四种：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语句有一个好处，就是可以实现特殊字符的自动转义。
４、 三个存储过程都有统一的构建规律，所以可以使用自动化工具依据表结构直接生成。
