2005中可以用 TRY...CATCH条件性语句(如 IF 语句)可以重置 @@ERROR。如果在 IF 语句中引用 @@ERROR,则在 IF 或 ELSE 块中对 @@ERROR 的引用将不检索 @@ERROR 信息。在下面的示例中,@@ERROR 通过 IF 重置,并且在 PRINT 语句中引用时不返回错误号。 复制代码 DECLARE @ErrorVar INTRAISERROR(N'Message', 16, 1); IF @@ERROR <> 0 -- This PRINT statement prints 'Error = 0' because -- @@ERROR is reset in the IF statement above. PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)); GO 下面的示例返回预期的结果。 复制代码 DECLARE @ErrorVar INTRAISERROR(N'Message', 16, 1); -- Save the error number before @@ERROR is reset by -- the IF statement. SET @ErrorVar = @@ERROR IF @ErrorVar <> 0 -- This PRINT statement correctly prints 'Error = 50000'. PRINT N'Error = ' + CAST(@ErrorVar AS NVARCHAR(8)); GO 如果要在运行语句之后同时引用 @@ERROR 和 @@ROWCOUNT,则必须在同一语句中引用它们。@@ERROR 和 @@ROWCOUNT 都可以使用每条 Transact-SQL 语句进行重置;因此,在对其中一个进行测试后,必须在同一语句中同时引用它们。在下面的示例中,@@ROWCOUNT 将始终为 0,因为它在被第一个 PRINT 语句重置之后才被引用。 复制代码 USE AdventureWorks; GO DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 13; -- This PRINT would successfully capture any error number. PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)); -- This PRINT will always print 'Rows Deleted = 0 because -- the previous PRINT statement set @@ROWCOUNT to 0. PRINT N'Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8)); GO 下面的示例返回预期的结果。 复制代码 USE AdventureWorks; GO DECLARE @ErrorVar INT; DECLARE @RowCountVar INT;DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 13; -- Save @@ERROR and @@ROWCOUNT while they are both -- still valid. SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT; IF (@ErrorVar <> 0) PRINT N'Error = ' + CAST(@ErrorVar AS NVARCHAR(8)); PRINT N'Rows Deleted = ' + CAST(@RowCountVar AS NVARCHAR(8)); GO
set @billNo=left(@bomSn,@billNoSep-1)--取得单据号你这句能够得到唯一主键吗? 如果不能的话,那么只有在你的写入数据的地方加入判断了,insert into zgssaleinfo (xsck_id,fgs_name,sp_name,kh_id,saledate,bx_time,hy_type,saler_id,jbr,sale_bz,address,admin,file_name) values(@billNo,@fgsname,@bomName,@kehu,@saledate,@bxtime,@hytype,@saler,@jbr,'',@address,@jbr,@filename) ---这里是一个处理错误的地方改为IF NOT EXISTS(SELECT * FROM zgssaleinfo WHERE xsck_id = (@billNo) BEGIN insert into zgssaleinfo (xsck_id,fgs_name,sp_name,kh_id,saledate,bx_time,hy_type,saler_id,jbr,sale_bz,address,admin,file_name) values(@billNo,@fgsname,@bomName,@kehu,@saledate,@bxtime,@hytype,@saler,@jbr,'',@address,@jbr,@filename) END ELSE BEGIN -- UPDATE .... END ---这里是一个处理错误的地方
DECLARE @ErrorVar INTRAISERROR(N'Message', 16, 1);
IF @@ERROR <> 0
-- This PRINT statement prints 'Error = 0' because
-- @@ERROR is reset in the IF statement above.
PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8));
GO
下面的示例返回预期的结果。 复制代码
DECLARE @ErrorVar INTRAISERROR(N'Message', 16, 1);
-- Save the error number before @@ERROR is reset by
-- the IF statement.
SET @ErrorVar = @@ERROR
IF @ErrorVar <> 0
-- This PRINT statement correctly prints 'Error = 50000'.
PRINT N'Error = ' + CAST(@ErrorVar AS NVARCHAR(8));
GO
如果要在运行语句之后同时引用 @@ERROR 和 @@ROWCOUNT,则必须在同一语句中引用它们。@@ERROR 和 @@ROWCOUNT 都可以使用每条 Transact-SQL 语句进行重置;因此,在对其中一个进行测试后,必须在同一语句中同时引用它们。在下面的示例中,@@ROWCOUNT 将始终为 0,因为它在被第一个 PRINT 语句重置之后才被引用。 复制代码
USE AdventureWorks;
GO
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 13;
-- This PRINT would successfully capture any error number.
PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8));
-- This PRINT will always print 'Rows Deleted = 0 because
-- the previous PRINT statement set @@ROWCOUNT to 0.
PRINT N'Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8));
GO
下面的示例返回预期的结果。 复制代码
USE AdventureWorks;
GO
DECLARE @ErrorVar INT;
DECLARE @RowCountVar INT;DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 13;
-- Save @@ERROR and @@ROWCOUNT while they are both
-- still valid.
SELECT @ErrorVar = @@ERROR,
@RowCountVar = @@ROWCOUNT;
IF (@ErrorVar <> 0)
PRINT N'Error = ' + CAST(@ErrorVar AS NVARCHAR(8));
PRINT N'Rows Deleted = ' + CAST(@RowCountVar AS NVARCHAR(8));
GO
服务器: 消息 2627,级别 14,状态 1,过程 AddSns2,行 49
[Microsoft][ODBC SQL Server Driver][SQL Server]违反了 PRIMARY KEY 约束 'PK__zgssaleinfo__27F8EE98'。不能在对象 'zgssaleinfo' 中插入重复键。
语句已终止。
set @billNo=left(@bomSn,@billNoSep-1)--取得单据号你这句能够得到唯一主键吗?
如果不能的话,那么只有在你的写入数据的地方加入判断了,insert into zgssaleinfo (xsck_id,fgs_name,sp_name,kh_id,saledate,bx_time,hy_type,saler_id,jbr,sale_bz,address,admin,file_name) values(@billNo,@fgsname,@bomName,@kehu,@saledate,@bxtime,@hytype,@saler,@jbr,'',@address,@jbr,@filename)
---这里是一个处理错误的地方改为IF NOT EXISTS(SELECT * FROM zgssaleinfo WHERE xsck_id = (@billNo)
BEGIN
insert into zgssaleinfo (xsck_id,fgs_name,sp_name,kh_id,saledate,bx_time,hy_type,saler_id,jbr,sale_bz,address,admin,file_name) values(@billNo,@fgsname,@bomName,@kehu,@saledate,@bxtime,@hytype,@saler,@jbr,'',@address,@jbr,@filename)
END
ELSE
BEGIN
-- UPDATE ....
END
---这里是一个处理错误的地方