别人写的存储过程 是直接接调用tgroup_to_group_ticheng_rate_bat_insert 不带参数吧,反复调用能不能出问题吧.我发现开始一次调用没有问题,在调用就提示 违反primary key 约束 禁止插入重复的值set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go--use transport
--select * from tuser_group a cross join tuser_group b where a.city_name!=b.city_name
ALTER procedure [dbo].[tgroup_to_group_ticheng_rate_bat_insert]
as
begin
declare @a int ,@b int
declare @a_city varchar(20),@b_city varchar(20)
DECLARE test CURSOR FOR
select a.id,b.id,a.city_name,b.city_name from tuser_group a cross join tuser_group b where a.city_name!=b.city_nameOPEN testFETCH NEXT FROM test
into @a, @b,@a_city,@b_city
WHILE @@FETCH_STATUS = 0
BEGIN
insert dbo.tgroup_to_group_ticheng_rate(fahuo_group_id,daohuo_group_id,transport_type) values(@a,@b,dbo.is_straight(@a_city,@b_city))
FETCH NEXT FROM test
into @a, @b,@a_city,@b_city
ENDCLOSE test
DEALLOCATE test
end
set QUOTED_IDENTIFIER ON
go--use transport
--select * from tuser_group a cross join tuser_group b where a.city_name!=b.city_name
ALTER procedure [dbo].[tgroup_to_group_ticheng_rate_bat_insert]
as
begin
declare @a int ,@b int
declare @a_city varchar(20),@b_city varchar(20)
DECLARE test CURSOR FOR
select a.id,b.id,a.city_name,b.city_name from tuser_group a cross join tuser_group b where a.city_name!=b.city_nameOPEN testFETCH NEXT FROM test
into @a, @b,@a_city,@b_city
WHILE @@FETCH_STATUS = 0
BEGIN
insert dbo.tgroup_to_group_ticheng_rate(fahuo_group_id,daohuo_group_id,transport_type) values(@a,@b,dbo.is_straight(@a_city,@b_city))
FETCH NEXT FROM test
into @a, @b,@a_city,@b_city
ENDCLOSE test
DEALLOCATE test
end
-----这里给个处理
if(not exists(select 1 from tgroup_to_group_ticheng_rate where fahuo_group_id=@a))
insert dbo.tgroup_to_group_ticheng_rate(fahuo_group_id,daohuo_group_id,transport_type) values(@a,@b,dbo.is_straight(@a_city,@b_city))
FETCH NEXT FROM test
into @a, @b,@a_city,@b_city
END
#2.存在则更新,不存在则插入
if exists(select 1 from dbo.tgroup_to_group_ticheng_rate where fahuo_group_id=@a)
update dbo.tgroup_to_group_ticheng_rate
set daohuo_group_id = @b,
transport_type = dbo.is_straight(@a_city,@b_city)
where fahuo_group_id=@a
else
insert dbo.tgroup_to_group_ticheng_rate(fahuo_group_id,daohuo_group_id,transport_type)
values(@a,@b,dbo.is_straight(@a_city,@b_city))