取@@ERROR永远都是0????

[mnc898]

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呢???

[xiaoku]

INSERT INTO Operate_Info (Operate_Name,Operate_State,Operate_Time,Operate_Error) VALUES ('ChkbuyersTime','失败',getdate(),@@ERROR)

RETURN @@ERROR

-------------
这个是因为你刚刚执行了插入语句,而且已经成功了,所以@@ERROR为 0 ...

[sp4]

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

[mnc898]

有方法了,因为@@ERROR只要执行一个操作其值就会变为了0,当执行
IF @@ERROR <> 0
后@@ERROR的值就变为0了.
所以我用定义一个变量来保存每次@@ERROR的值的方法

[mnc898]

不知道楼下有没有更好的方法,感觉这个办法很土啊!!!

[北京的雾霾天]

@@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