最近做项目接触采购部分，设计采购订单表分为采购订单主表和采购订单子表，要求写一个存储过程，实现添加和修改（事务处理），并在添加一条记录到采购订单主表时，有可能添加多条记录到采购订单子表。先写上存储过程，再做讲解。
代码
1 set ANSI_NULLS ON
2 set QUOTED_IDENTIFIER ON
3 go
4
5 --exec sp_uf_savePurchaseOrder
6 ALTER procedure [dbo].[sp_uf_savePurchaseOrder]
7 (
8 @PurchaseOrderID int,
9 @SysNO nvarchar(20),
10 @PurchaseOrderNO nvarchar(20),
11 @SignOrderDate datetime,
12 @OrderPeriod int,
13 @OrderAmount decimal, --订单金额
14 @OrderProductDate datetime, --订货日期
15 @DeliveryProductDate datetime, --交货日期
16 @DeliveryProductPlace nvarchar(200), --交货地点
17 @DiscountInfo nvarchar(200), --折扣信息
18 @Remark nvarchar(200), --备注
19 @Create_Date datetime, --制单日期
20 @SupplierID int, --供应方ID
21 @CompanyID int, --购入方ID
22 @ProjectID int, --使用方ID
23 @PaymentTypeID int, --付款方式ID
24 @PaymentConditionID int, --付款条件ID
25 @InvoiceTypeID int, --发票形式ID
26 --@PurchaseOrderDID int, --采购订单子表ID
27 --@PurchaseQty int, --采购数量
28 --@PurchasePrice decimal, --采购单价
29 @strOrderDetail nvarchar(500) --子项ID,字符串形式
30 )
31 as
32 begin
33
34 declare @intID_Order int;
35 select @intID_Order = (ISNULL(Max(PurchaseOrderID),0)+ 1) from PurchaseOrderHeader;
36
37 --declare @intID_OrderDetail int;
38 --select @intID_OrderDetail = (ISNULL(Max(PurchaseOrderDID),0)+ 1) from PurchaseOrderDetail;
39
40 if not exists(select 1 from PurchaseOrderHeader (nolock) where PurchaseOrderID=@PurchaseOrderID)
41
42 begin
43
44 begin tran t1
45
46 insert into PurchaseOrderHeader(PurchaseOrderID,SysNO,PurchaseOrderNO,SignOrderDate,OrderPeriod,
47 OrderAmount,OrderProductDate,DeliveryProductDate,DeliveryProductPlace,DiscountInfo,
48 Remark,Create_Date,SupplierID,CompanyID,InvoiceTypeID,PaymentConditionID,PaymentTypeID,
49 ProjectID,DataStatus,Create_UID,Update_UID,Update_Date,Disuse_UID,Disuse_Date)
50 values(@intID_Order,@SysNO,@PurchaseOrderNO,@SignOrderDate,@OrderPeriod,@OrderAmount,@OrderProductDate,
51 @DeliveryProductDate,@DeliveryProductPlace,@DiscountInfo,@Remark,@Create_Date,
52 @SupplierID,@CompanyID,@InvoiceTypeID,@PaymentConditionID,@PaymentTypeID,@ProjectID,
53 1,1,1,getdate(),1,getdate())
54
55 if @@error <> 0
56 begin
57 rollback tran
58 return -11
59 end
60
61 select id,dbo.Get_StrArrayStrOfIndex(part_string,',',1) as ProductID,
62 dbo.Get_StrArrayStrOfIndex(part_string,',',2) as PurchaseQty,
63 dbo.Get_StrArrayStrOfIndex(part_string,',',3) as PurchasePrice
64 into #tmpInsertDetail
65 from fn_NSplit(@strOrderDetail,'/')
66 --select * from #tmpInsertDetail
67
68 if @@error <> 0
69 begin
70 rollback tran
71 return -12
72 end
73
74 insert into PurchaseOrderDetail(PurchaseOrderID,ProductID,PurchaseQty,PurchasePrice)
75 select @intID_Order,ProductID,PurchaseQty,PurchasePrice from #tmpInsertDetail
76
77 drop table #tmpInsertDetail
78
79 if @@error <> 0
80 begin
81 rollback tran
82 return -13
83 end
84
85 commit tran
86
87 end
88
89 else
90 begin
91
92 begin tran t2
93
94 update PurchaseOrderHeader set SignOrderDate=@SignOrderDate,
95 OrderPeriod=@OrderPeriod,OrderAmount=@OrderAmount,OrderProductDate=@OrderProductDate,
96 DeliveryProductDate=@DeliveryProductDate,DeliveryProductPlace=@DeliveryProductPlace,
97 DiscountInfo=@DiscountInfo,Remark=@Remark,Create_Date=@Create_Date,
98 SupplierID=@SupplierID,CompanyID=@CompanyID,InvoiceTypeID=@InvoiceTypeID,
99 PaymentConditionID=@PaymentConditionID,PaymentTypeID=@PaymentTypeID,
100 ProjectID=@ProjectID,DataStatus=1,Create_UID=1,Update_UID=1,Update_Date=getdate(),
101 Disuse_UID=1,Disuse_Date=getdate()
102 where PurchaseOrderID=@PurchaseOrderID
103
104 if @@error <> 0
105 begin
106 rollback tran
107 return -21
108 end
109
110 select id,dbo.Get_StrArrayStrOfIndex(part_string,',',1) as ProductID,
111 dbo.Get_StrArrayStrOfIndex(part_string,',',2) as PurchaseQty,
112 dbo.Get_StrArrayStrOfIndex(part_string,',',3) as PurchasePrice
113 into #tmpUpdateDetail
114 from fn_NSplit(@strOrderDetail,'/')
115 --select * from #tmpUpdateDetail
116
117 if @@error <> 0
118 begin
119 rollback tran
120 return -22
121 end
122
123 delete from PurchaseOrderDetail where PurchaseOrderID=@PurchaseOrderID
124
125 if @@error <> 0
126 begin
127 rollback tran
128 return -23
129 end
130
131 insert into PurchaseOrderDetail(PurchaseOrderID,ProductID,PurchaseQty,PurchasePrice)
132 select @PurchaseOrderID,ProductID,PurchaseQty,PurchasePrice from #tmpUpdateDetail
133
134 drop table #tmpUpdateDetail
135
136 if @@error <> 0
137 begin
138 rollback tran
139 return -24
140 end
141
142 commit tran
143
144 end
145
146 end
147
此存储过程用到两个函数：
dbo.Get_StrArrayStrOfIndex（标量值函数）和fn_NSplit（表值函数）
dbo.Get_StrArrayStrOfIndex：
代码
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER function [dbo].[Get_StrArrayStrOfIndex]
(
@str varchar(1024), --要分割的字符串
@split varchar(10), --分隔符号
@index int --取第几个元素
)
returns varchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int
set @str = ltrim(rtrim(@str))
set @start = 1
set @next = 1
set @seed = len(@split)
set @location = charindex(@split, @str)
while @location <> 0 and @index > @next
begin
set @start = @location + @seed
if charindex(@split, @str, @start) = 0 and @next > 1
return substring(@str, @start, @start)
set @location = charindex(@split, @str, @start)
set @next = @next + 1
end
if @location = 0
begin
select @location = len(@str) + 1
set @start = @location
end
--这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。
return substring(@str, @start, @location - @start)
end
fn_NSplit
代码
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[fn_NSplit]
(
@pstr AS NVARCHAR(MAX),
@delimiter NCHAR(1)
)
RETURNS @split_string TABLE
(
Id INT IDENTITY (1,1),
part_string NVARCHAR(4000)
)
AS
BEGIN
declare @l_len bigint
declare @l_pr bigint
declare @l_pos bigint
declare @l_Str4K nvarchar(4000)
declare @l_Last4KPeicePos bigint
declare @l_LastItem nvarchar(100)
select @l_Str4K = substring (@pstr, 1, 4000), @l_Last4KPeicePos = 4001
while len (@l_Str4K) = 4000 begin
select @l_pr=1, @l_pos = charindex (@delimiter, @l_Str4K, @l_pr)
while (@l_pos != 0) begin
insert @split_string (part_string)
values (substring (@l_Str4K, @l_pr, @l_pos - @l_pr))
select @l_pr = @l_pos + 1
select @l_pos = charindex (@delimiter, @l_Str4K, @l_pr)
end
if (@l_pr <= 4000) begin
select @l_Str4K = substring (@l_Str4K, @l_pr, 4000)
select @l_Last4KPeicePos = @l_Last4KPeicePos + 4000 - len (@l_Str4K)
select @l_Str4K = @l_Str4K + substring (@pstr, @l_Last4KPeicePos - (4000 - len (@l_Str4K)), 4000 - len (@l_Str4K))
end
else begin
select @l_Str4K = substring (@pstr, @l_Last4KPeicePos, 4000),
@l_Last4KPeicePos = @l_Last4KPeicePos + 4000
end
end
-- Parse the last peice
select @l_pr=1, @l_pos = charindex (@delimiter, @l_Str4K, @l_pr)
while (@l_pos != 0) begin
insert @split_string (part_string)
values (substring (@l_Str4K, @l_pr, @l_pos - @l_pr))
select @l_pr = @l_pos + 1
select @l_pos = charindex (@delimiter, @l_Str4K, @l_pr)
end
-- add the last item
select @l_LastItem = substring (@l_Str4K, @l_pr, 100)
if len (@l_LastItem) > 0 begin
insert @split_string (part_string)
values (@l_LastItem)
end
return
END
这两个函数的使用可以用一个小例子解释：
代码
select id,dbo.Get_StrArrayStrOfIndex(part_string,',',1) as ProductID,
dbo.Get_StrArrayStrOfIndex(part_string,',',2) as PurchaseQty,
dbo.Get_StrArrayStrOfIndex(part_string,',',3) as PurchasePrice
into #tmpUpdateDetail
from fn_NSplit('123,22.5,33/12,255.6,1/1334,3.4,1233/','/')
drop table #tmpUpdateDetail
select * from #tmpUpdateDetail
select * from fn_NSplit('123,22.5,33/12,255.6,1/1334,3.4,1233/','/')
那么，上面的存储过程内，传入一个字符串参数，来代替上面例子里手动输入的字符串，在项目代码后台，用循环方式把字符串用“，”、“/”隔开，即可。