取@@ERROR永远都是0????
CREATE PROCEDURE dbo.ChkbuyersTime
AS
declare @i int,@RA_User int,@RA_HighPrice int,@AL_Rate float,@str nvarchar(1000),@price int,@RA_Domain int,@RA_DomainUser int,@maxid int
BEGIN TRANSACTION GetDataSet
CREATE TABLE #TemporaryTable (
Row int IDENTITY(1,1) PRIMARY KEY,
RA_User int,RA_HighPrice int,RA_domain int,RA_DomainUser int
)
IF @@ERROR <> 0
GOTO ErrorHandler
SET @str = ' INSERT INTO #TemporaryTable1 '
SET @str = @str + ' SELECT [RA_User],[RA_HighPrice],[RA_Domain],[RA_DomainUser] FROM RivalAct join domains on (d_id = RA_Domain and D_user = RA_DomainUser) join domainsell on d_id = ds_domain where D_SellState =1 and DS_TradeType = 3 and RA_State = 1 and DATEDIFF (s,DATEADD(d,5,RA_Time),getdate())> =0 '
EXEC(@str)
IF @@ERROR <> 0
GOTO ErrorHandler
DROP TABLE #TemporaryTable
COMMIT TRANSACTION GetDataSet
INSERT INTO Operate_Info (Operate_Name,Operate_State,Operate_Time) VALUES ( 'ChkbuyersTime ', '成功 ',getdate())
RETURN 0
ErrorHandler:
ROLLBACK TRANSACTION GetDataSet
INSERT INTO Operate_Info (Operate_Name,Operate_State,Operate_Time,Operate_Error) VALUES ( 'ChkbuyersTime ', '失败 ',getdate(),@@ERROR)
RETURN @@ERROR
GO
为什么在失败时@@ERROR取出来还是0呢???
[解决办法]
INSERT INTO Operate_Info (Operate_Name,Operate_State,Operate_Time,Operate_Error) VALUES ( 'ChkbuyersTime ', '失败 ',getdate(),@@ERROR)
RETURN @@ERROR
-------------
这个是因为你刚刚执行了插入语句,而且已经成功了,所以@@ERROR为 0 ...
[解决办法]
ErrorHandler:
declare @error int
set @error= @@ERROR
ROLLBACK TRANSACTION GetDataSet
INSERT INTO Operate_Info (Operate_Name,Operate_State,Operate_Time,Operate_Error) VALUES ( 'ChkbuyersTime ', '失败 ',getdate(),@@ERROR)
RETURN @error
[解决办法]
@@ERROR 只返回上一个语句出错后的代码.
[解决办法]
可以將錯誤信息放到一張表pubMessage中保存
然後自定義一下函數GetMsg_fn來調用
CREATE FUNCTION [dbo].[GetMsg_fn](@msgid varchar(5),@msglang varchar(10))
RETURNS nvarchar(200)
AS
BEGIN
DECLARE @msgText nvarchar(200)
declare @ErrStr varchar(200)
set @msgText= ' '
select @msgText=msgText from pubMessage where msgid=@msgid and msglang=@msglang
return @msgText
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Att_DayoffTypeNewEdt_sp]
@pLang char(10)= ' ',
@pTranType varchar(30)=null,
@DayoffTypeID smallint =0,
@DayoffTypeNo varchar(20) = ' ',
@DayoffTypeName varchar(30) = ' ',
@DayoffIsGongshang char(2) = ' ',
@NewCode varchar(50)= ' '
AS
/*Declare*/
DECLARE @ErrStr nvarchar(200)
IF @pTranType IS NULL
BEGIN
SELECT @ErrStr=dbo.GetMsg_fn( 'E0001 ',@pLang) --Transaction type can not be NULL.
RAISERROR(@ErrStr,11,1)
RETURN 100
END
SET @pTranType=upper(@pTranType)
IF @pTranType = 'NEW '
BEGIN
IF (SELECT count(1) FROM Att_DayoffType (nolock)
WHERE DayoffTypeID=@DayoffTypeID)> 0
BEGIN
SELECT @ErrStr=dbo.GetMsg_fn( 'E0004 ',@pLang) --has been used.
SET @ErrStr= 'DayoffTypeID: '+ @DayoffTypeID+ ' '+@ErrStr
RAISERROR(@ErrStr,11,1)
RETURN 101
END
BEGIN
INSERT INTO Att_DayoffType WITH (ROWLOCK)(
DayoffTypeNo,
DayoffTypeName,
DayoffIsGongshang
) VALUES (
@DayoffTypeNo,
@DayoffTypeName,
@DayoffIsGongshang
)
RETURN 0
END
END
IF @pTranType = 'EDIT '
BEGIN
IF (SELECT count(1) FROM Att_DayoffType (nolock)
WHERE DayoffTypeID=@DayoffTypeID)=0
BEGIN
SELECT @ErrStr=dbo.GetMsg_fn( 'E0003 ',@pLang)
SET @ErrStr=@ErrStr + 'DayoffTypeID ' --Can not find this Col_ID
RAISERROR(@ErrStr,11,1)
RETURN 102
END
/*
IF @DayoffTypeID <> @NewCode --如果修改了PK
BEGIN
INSERT INTO pubCodeModify (tablename,oldcode,oldcname,oldename,newcode,newcname,newename,modifytype,lupby,lupdt)
SELECT 'Att_DayoffType ' AS tablename,
DayoffTypeID AS oldcode,
cname AS oldcname,
ename AS oldename,
@newcode as newcode,
@cname AS newcname,
@ename AS newename,
'M ' AS modifytype,
@lupby AS lupby,
getdate() as lupdt
FROM Att_DayoffType
WHERE DayoffTypeID=@DayoffTypeID
END
*/
UPDATE Att_DayoffType WITH (ROWLOCK) SET
DayoffTypeNo=@DayoffTypeNo,
DayoffTypeName=@DayoffTypeName,
DayoffIsGongshang=@DayoffIsGongshang
WHERE DayoffTypeID=@DayoffTypeID
RETURN 0
END
/*If it goes here,then the transaction type passed is invalid.*/
BEGIN
SELECT @ErrStr=dbo.GetMsg_fn( 'E0002 ',@pLang)
RAISERROR (@ErrStr,11,1)
RETURN 103
END
