Question

SQL存储过程 循环插入结束后再插入一条数据到其他表

Create Procedure SqlTrans
declare @Count int
@MaxCount int,
@TaxId varChar(20),
@NsrId varChar(20),
@NsrName varChar(20),
@Division varChar(20),
@Question varChar(200),
@StarTime datetime,
@endtime datetime,
@Tax varChar(20),

@TaxName varChar(20),
@Sources int,
@souway int,
@details int,
@about varchar(200),
@jmbz int,
@checkinDate datetime,
@master varChar(20)
as
set @Count = 0;
while
(@Count < @MaxCount)
Begin
BEGIN TRAN
set @Count = @Count +1;
INSERT INTO [TE].[dbo].[payers]
values(@TaxId,@NsrId,@NsrName,@Division,@Question,@StarTime,@endtime,@Tax)
END
begin
insert into [TE].[dbo].[TaxCheckIn]
values(@TaxId,@TaxName,@Sources,@souway,@details,@about,@jmbz,@checkinDate,@master)
END
if @@ERROR = 0
COMMIT TRAN
else
ROLLBACK TRAN

Answer 1

改改语法问题，楼主看看是否正确

Create Procedure SqlTrans
(
@MaxCount int,
@TaxId varChar(20),
@NsrId varChar(20),
@NsrName varChar(20),
@Division varChar(20),
@Question varChar(200),
@StarTime datetime,
@endtime datetime,
@Tax varChar(20),
@TaxName varChar(20),
@Sources int,
@souway int,
@details int,
@about varchar(200),
@jmbz int,
@checkinDate datetime,
@master varChar(20)
)
AS
DECLARE @Count INT
SET @Count=0

WHILE @Count < @MaxCount
Begin
BEGIN TRAN
set @Count = @Count +1;
INSERT INTO [TE].[dbo].[payers]
values(@TaxId,@NsrId,@NsrName,@Division,@Question,@StarTime,@endtime,@Tax)

if @@ERROR <> 0
GOTO Error
END
begin
insert into [TE].[dbo].[TaxCheckIn]
values(@TaxId,@TaxName,@Sources,@souway,@details,@about,@jmbz,@checkinDate,@master)

if @@ERROR <> 0
GOTO Error
END

COMMIT TRAN
return
ERROR:
ROLLBACK TRAN