CREATE PROCEDURE [dbo].[sp_test] @p_userid varchar(4) AS declare @id varchar(10) declare @af1 varchar(10) declare @af2 varchar(10) declare @af3 varchar(10) declare @bf4 varchar(10) declare @userid varchar(10) declare @sql varchar(400)declare mycur Cursor for select id, af1,af2,af3,bf4,userid from 表a open mycur fetch next from mycur into @id, @af1, @af2, @af3, @bf4, @userid while (1=1) begin print 'begin' if @@fetch_status<> 0 break if exists(select * from 表c where cf1 = @af1 and cf2 = @af2) begin print '1' update 表c set cf3 = @af3 insert into 表b select @id, @af1, @af2, @af3, 0, @p_userid end else begin print '0' insert into 表c select @af1, @af2, @af3 insert into 表b select @id, @af1, @af2, @af3, 1, @p_userid end fetch next from mycur into @id, @af1, @af2, @af3, @bf4, @userid end close mycur deallocate mycur select * from 表a select * from 表b select * from 表c GO
楼主的意思是不是没说全呢?是不是对表b操作的时候,也要判断是不是已经存在bf1=af1 and bf2=af2的情况?
@p_userid varchar(4)
AS
declare @id varchar(10)
declare @af1 varchar(10)
declare @af2 varchar(10)
declare @af3 varchar(10)
declare @bf4 varchar(10)
declare @userid varchar(10)
declare @sql varchar(400)declare mycur Cursor for
select id, af1,af2,af3,bf4,userid from 表a
open mycur
fetch next from mycur into @id, @af1, @af2, @af3, @bf4, @userid
while (1=1)
begin
print 'begin'
if @@fetch_status<> 0 break
if exists(select * from 表c where cf1 = @af1 and cf2 = @af2)
begin
print '1'
update 表c set cf3 = @af3
insert into 表b select @id, @af1, @af2, @af3, 0, @p_userid
end
else
begin
print '0'
insert into 表c select @af1, @af2, @af3
insert into 表b select @id, @af1, @af2, @af3, 1, @p_userid
end
fetch next from mycur into @id, @af1, @af2, @af3, @bf4, @userid
end
close mycur
deallocate mycur
select * from 表a
select * from 表b
select * from 表c
GO