22,210
社区成员
发帖
与我相关
我的任务
分享
if exists (select 1
from sysobjects
where id = object_id('proceaccresscard_add')
and type in ('P','PC'))
drop procedure proceaccresscard_add
go
create procedure proceaccresscard_add
@acicode varchar(20),
@acimsg varchar(200),
@ciid int,
@strdeviceid varchar(500) --‘1,2,3,4,5。。。。。’
as
declare
@aciid int,
@error int,
@devicestart int, --总长度
@deviceindex int, --当前索引
@deviceid int
begin
set @error=0
begin tran --声明事务
insert into accresscardinfor (acicode,acimsg) values(@acicode,@acimsg)
set @error=@error+@@error
set @aciid=@@identity
if @aciid>0 and @ciid>0
begin --映射设备与社区(room)的关系
insert into mappingacc (ciid,aciid) values(@ciid,@aciid)
set @error=@error+@@error
if @@rowcount>0 and @deviceid >0--mappingacc 添加成功
begin --映射设备与卡的关系
set @deviceindex=1
set @devicestart=0
while (@deviceindex<len(@strdeviceid))
begin
set @devicestart= charindex(',',@strdeviceid,@deviceindex) --得到起始位置
if @devicestart<1 --无效起始位置
begin
set @error = @error+1
end
set @deviceid = cast(substring(@strdeviceid,@deviceindex,@devicestart-@deviceindex) as int) --得到设备id
set @deviceindex=@devicestart+1 --索引+1 到下一个起始位置
if @deviceid<1 --无效设备id
begin
set @error = @error+1
end
insert into mappingacd (deviceid,aciid) values(@deviceid,@aciid) --映射卡与设备的关系
set @error=@error+@@error
end
end
end
if @error=0
begin
commit tran
end
else
begin
rollback tran
end
end
go