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 0ErrorHandler:
ROLLBACK TRANSACTION GetDataSet
INSERT INTO Operate_Info (Operate_Name,Operate_State,Operate_Time,Operate_Error) VALUES ('ChkbuyersTime','失败',getdate(),@@ERROR)
RETURN @@ERROR
GO为什么在失败时@@ERROR取出来还是0呢???
解决方案 »
- MSsql中default的用法
- SQL 2008里怎么设置级联删除?
- CASE when Null 问题
- 非常棘手的重复记录问题,大大帮忙解决啊
- sql server服务管理器中的服务器名称和企业管理器的服务器属性中的“名称”不一样,什么原因?
- 如果中文字符是通过参数传入存储过程执行,如何在存储过程中达到“insert into t(a, b) values(N'简体', N'繁体')”的效果?
- SQL 出现的天数统计
- 请教高手,C/S与B/S结合项目的一个数据库问题!!!!!!
- SQL Server7中怎么恢复数据
- 熟悉oracle的请进
- 求一个简单的SQL语句!
- 表字段选用的问题(字段的区别,unicode等编码的区别)
这个是因为你刚刚执行了插入语句,而且已经成功了,所以@@ERROR为 0 ...
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只要执行一个操作其值就会变为了0,当执行
IF @@ERROR <> 0
后@@ERROR的值就变为0了.
所以我用定义一个变量来保存每次@@ERROR的值的方法
然後自定義一下函數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
ENDset 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
ENDIF @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