Question

SQL的存储过程里,其中一条语句出现错误,如何让整个存储过程都不执行

我建了一个存储过程,其中有6条insert语句.
我想让它 只要其中一条出错就不能执行整个存储过程.要怎么做?
ALTER PROCEDURE [dbo].[Chejiaxinxi]
@frameid int ,
@frametype smallint,
@dateT smalldatetime,
@HJ VArchar(5),
@WBZL char(1),
@NBZL char(1),
@HJGS int,
@ZP1 VArchar(5),
@ZP2 VArchar(5)=null,
@ZP3 VArchar(5)=null,
@ZP4 VArchar(5)=null,
@zpcount tinyint,
@ZPGS int ,
@XJ1 VArchar(5),
@XJ2 VArchar(5) = null ,
@xjcount tinyint,
@XJGS int
AS
begin
SET NOCOUNT ON;

INSERT INTO [banhandb].[Production].[ChejiaProductHeader]([FrameID],[FrametypeID],[Date])VALUES(@frameid,@Frametype,@datet)
INSERT INTO [banhandb].[Production].[ChejiaProductRegister]([FrameID],[PersonNumber],[OperationID],[OutsideQuality],[InsideQuality],[Count],[ManHour])VALUES(
@frameid,@hj,1,@wbzl,@nbzl,1,@hjgs )
INSERT INTO [banhandb].[Production].[ChejiaProductRegister]([FrameID],[PersonNumber],[OperationID],[Count],[ManHour])VALUES(
@FrameID,@zp1,2,@zpcount,@zpgs)
if @zp2 is not null
begin
INSERT INTO [banhandb].[Production].[ChejiaProductRegister]([FrameID],[PersonNumber],[OperationID],[Count],[ManHour])VALUES(
@frameid,@zp2,2,@zpcount,@zpgs)
end
if @zp3 is not null
begin
INSERT INTO [banhandb].[Production].[ChejiaProductRegister]([FrameID],[PersonNumber],[OperationID],[Count],[ManHour])VALUES(
@frameid,@zp3,2,@zpcount,@zpgs)
end
if @zp4 is not null
begin
INSERT INTO [banhandb].[Production].[ChejiaProductRegister]([FrameID],[PersonNumber],[OperationID],[Count],[ManHour])VALUES(
@frameid,@zp4,2,@zpcount,@zpgs)
end
INSERT INTO [banhandb].[Production].[ChejiaProductRegister]([FrameID],[PersonNumber],[OperationID],[Count],[ManHour])VALUES(
@frameid,@xj1,3,@xjcount,@xjgs)
if @xj2 is not null
begin
INSERT INTO [banhandb].[Production].[ChejiaProductRegister]([FrameID],[PersonNumber],[OperationID],[Count],[ManHour])VALUES(
@frameid,@xj2,3,@xjcount,@xjgs)
-- Insert statements for procedure here
end
end
这回答也太简单了

Answer 1

要用禅卖到事务处理
你每执行一个操作都会有一个@@error的系统变量
所以你可以根据这个系统变量来判断你的语句执行是否成功。
若是成功则提交失误，否则回滚事物就渣纯可以了。

declare @error int
begin transaction
SQL语句
@error=@error+@@erorr
...
if @error>0
rollback transaction
else
commit transaction
GO

就是这样子贺梁逗了

Answer 2

begin transaction

每执行一条碧桥姿语句把错误号悔绝累加 在最后判消正断

if @@error <> 0
rollback transaction
else
commit transaction

Answer 3

tran
rollback

http://msdn.microsoft.com/en-us/library/ms188929.aspx

Answer 4

ALTER PROCEDURE [dbo].[Chejiaxinxi]
@frameid int ,
@frametype smallint,
@dateT smalldatetime,
@HJ VArchar(5),
@WBZL char(1),
@NBZL char(1),
@HJGS int,
@ZP1 VArchar(5),
@ZP2 VArchar(5)=null,
@ZP3 VArchar(5)=null,
@ZP4 VArchar(5)=null,
@zpcount tinyint,
@ZPGS int ,
@XJ1 VArchar(5),
@XJ2 VArchar(5) = null ,
@xjcount tinyint,
@XJGS int
AS
begin
SET NOCOUNT ON;

BEGIN TRAN
INSERT INTO [banhandb].[Production].[ChejiaProductHeader]([FrameID],[FrametypeID],[Date])VALUES(@frameid,@Frametype,@datet)
INSERT INTO [banhandb].[Production].[ChejiaProductRegister]([FrameID],[PersonNumber],[OperationID],[OutsideQuality],[InsideQuality],[Count],[ManHour])VALUES(
@frameid,@hj,1,@wbzl,@nbzl,1,@hjgs )

INSERT INTO [banhandb].[Production].[ChejiaProductRegister]([FrameID],[PersonNumber],[OperationID],[Count],[ManHour])VALUES(
@FrameID,@zp1,2,@zpcount,@zpgs)

if @zp2 is not null
begin
INSERT INTO [banhandb].[Production].[ChejiaProductRegister]([FrameID],[PersonNumber],[OperationID],[Count],[ManHour])VALUES(
@frameid,@zp2,2,@zpcount,@zpgs)
end

if @zp3 is not null
begin
INSERT INTO [banhandb].[Production].[ChejiaProductRegister]([FrameID],[PersonNumber],[OperationID],[Count],[ManHour])VALUES(
@frameid,@zp3,2,@zpcount,@zpgs)
end
if @zp4 is not null
begin
INSERT INTO [banhandb].[Production].[ChejiaProductRegister]([FrameID],[PersonNumber],[OperationID],[Count],[ManHour])VALUES(
@frameid,@zp4,2,@zpcount,@zpgs)
end
INSERT INTO [banhandb].[Production].[ChejiaProductRegister]([FrameID],[PersonNumber],[OperationID],[Count],[ManHour])VALUES(
@frameid,@xj1,3,@xjcount,@xjgs)

if @xj2 is not null
begin
INSERT INTO [banhandb].[Production].[ChejiaProductRegister]([FrameID],[PersonNumber],[OperationID],[Count],[ManHour])VALUES(
@frameid,@xj2,3,@xjcount,@xjgs)
-- Insert statements for procedure here
end
IF @@ERROR=0
COMMIT TRAN
ELSE
ROLLBACK TRAN
end

Answer 5

事务日志