Sql server存储过程以及一次插入多条记录
首先简单介绍sql server存储过程吧。至于概念含义啥的就不做过多介绍了。它其实和mysql有些类似，语法大同小异。还是用例子说明问题吧。
CREATE PROCEDURE insert_supplier
@supplier_name varchar(30),
@supplier_contact varchar(30),
--------------------------------输入参数
@theoutputstring varchar(50) output
-------------------------------输出参数
AS
BEGIN TRAN
SET XACT_ABORT OFF
-------------------------------事务操作开始与 SET XACT_ABORT ON
-------------------------------和if (@theresult<=0) ROLLBACK TRAN else COMMIT TRAN
-------------------------------构成事务的完整操作
declare @theresult int
set @theresult=1
-------------------------------定义和初始化变量
-------------------------------做一些基本的判定
if (@theresult>=0)
begin
--------------------------------------------------------------------------------------begin
if (@theresult=1)
begin
insert into supplier
(supplier_name,supplier_contact,supplier_phone,supplier_fax,supplier_email,
supplier_postcode,supplier_remark,supplier_address,
supplier_website,supplier_telephone)
values
(@supplier_name,@supplier_contact,@supplier_phone,@supplier_fax,@supplier_email,
@supplier_postcode,@supplier_remark,@supplier_address,
@supplier_website,@supplier_telephone)
if (@@rowcount<>1) set @theresult=-2
end
------------------------------------------------------------------------------------end
end
SET XACT_ABORT ON
if (@theresult<=0) ROLLBACK TRAN else COMMIT TRAN
------------------------------------------------------事务提交或回滚
------------------------------------------------------生成出错信息
if @theresult=0 set @theoutputstring='其他错误'
else
if @theresult=-2 set @theoutputstring='信息修改错误'
else
set @theoutputstring='true'
-----------------------------------------------------输出结果集
select @theoutputstring
GO
根据红色的详细注释，相信大家不难理解这个存储过程的基本语法，其实和mysql是差不多的。因为sql server是支持事务的，所以每个执行都是可以回滚的和mysql的innodb引擎一样。接下来说下如何使用支持插入多条数据的操作，因为sql并没有数组的概念，所以只能用一些字符串拼接并解析的方法来处理。set @prepoint_price = 1set @curpoint_price = charindex('$',@tmp_sale_price)就以这个销售价格为例，如果我想传多条记录中的多个价格，那么可以传之前先把需要传的价格拼接起来，并用一个特殊字符$来间隔。PS：charindex这是定位符号函数，返回第一个此特殊字符的位置。set @sale_price = substring(@tmp_sale_price,@prepoint_price,@curpoint_price-@prepoint_price)然后设置新变量使用substring函数，以上那句就是在tmp_sale_price变量中截取开始到特殊字符为止的变量，这样就可以很轻松获得多条销售价格中的一条。以此类推，就可以不断分析出所有的变量值。最后给出一个简单例子来说明，大家可以其结构，至于业务逻辑可以忽视。
@sale_id varchar(20),
@client_name varchar(128),
@employee_id int,
@transaction_id int,
@warehouse_id varchar(32),
@sale_time datetime,
@sale_num varchar(5),
@sale_totalprice float(8),
@sale_logistics varchar(40),
@sale_logisticscode varchar(40),
@tmp_localitem_id varchar(600),
@tmp_sale_price varchar(600),
@theoutputstring varchar(50) output
AS
BEGIN TRAN
SET XACT_ABORT OFF
declare @theresult int,@curpoint int, @prepoint int,@curpoint_price int, @prepoint_price int, @localitem_id varchar(20),@sale_price varchar(50)
set @theresult=0
if (@theresult>=0)
begin
insert into sale_record(sale_id,client_name,employee_id,transaction_id,warehouse_id,sale_time,sale_num,sale_totalprice,sale_logistics,sale_logisticscode)
values(@sale_id,@client_name,@employee_id,@transaction_id,@warehouse_id,@sale_time,@sale_num,@sale_totalprice,@sale_logistics,@sale_logisticscode)
if (@@rowcount<>1) set @theresult=-2
end
if(@theresult>=0)
begin
set @prepoint = 1
set @curpoint = charindex('$',@tmp_localitem_id)
set @prepoint_price = 1
set @curpoint_price = charindex('$',@tmp_sale_price)
while @curpoint>0 and @theresult>=0
begin
set @localitem_id = substring(@tmp_localitem_id,@prepoint,@curpoint-@prepoint)
set @sale_price = substring(@tmp_sale_price,@prepoint_price,@curpoint_price-@prepoint_price)
insert into sale_detail(localitem_id,transaction_id,sale_id,sale_price)values(@localitem_id,@transaction_id,@sale_id,@sale_price)
if (@@rowcount<>1) set @theresult=-3
if(@theresult>=0)
begin
update storage set localitem_num = '0' where localitem_id = @localitem_id
if (@@rowcount<>1) set @theresult=-4
end
set @prepoint=@curpoint+1
set @prepoint_price=@curpoint_price+1
set @curpoint=charindex('$',@tmp_localitem_id,@prepoint)
set @curpoint_price=charindex('$',@tmp_sale_price,@prepoint_price)
end
end
SET XACT_ABORT ON
set @theresult=1
if (@theresult<=0) ROLLBACK TRAN else COMMIT TRAN
if @theresult=0 set @theoutputstring='其他错误'
else
if @theresult=-2 set @theoutputstring='信息修改错误'
else
if @theresult=-3 set @theoutputstring='查询详细表有误'
else
if @theresult=-2 set @theoutputstring='修改库存有误'
else
if @theresult=1 set @theoutputstring='true'
select @theoutputstring