代码之家 › 专栏 › 技术社区 › IsmailS

使用RAISERROR在SQLServer2005中不起作用?

stored-procedures sql-server-2005 sql-server

7

IsmailS · 技术社区 · 15 年前

我有以下代码:

BEGIN TRY 

    BEGIN TRANSACTION
       -- DO SOMETHIING

    COMMIT TRAN

END TRY
BEGIN CATCH
   IF(@@TRANCOUNT > 0)
     ROLLBACK TRANSACTION

   RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE()) --ERROR: Incorrect syntax near 'ERROR_MESSAGE'.

END CATCH

但是,RAISERROR语句不起作用。raise error语句中有什么错误?

2 回复 | 直到 12 年前

1

8

Joe Stefanelli 15 年前

RAISERROR遵循与任何其他存储过程调用相同的规则。传入的参数必须是常量或变量。不能将函数直接作为参数传递。看到了吗 Executing Stored Procedures 这方面的文件。

/* Demo Code - Functions accept functions as parameters
               while stored procedures do not              */

create function dbo.fnDayOfWeek 
    (@date datetime) 
    returns int
as
begin
    declare @x int
    set @x = DATEPART(day,@date)
    return (@x)
end
go

/* Both statements are successful */
select dbo.fnDayOfWeek('2010-08-06')
go
select dbo.fnDayOfWeek(GETDATE())
go

drop function dbo.fnDayOfWeek
go

create procedure DayOfWeek
    @date datetime
as
begin
    select DATEPART(day,@date)
end
go

/* First call succeeds, second fails */
exec DayOfWeek @date = '2010-08-06'
go
exec DayOfWeek @date = getdate()
go

drop procedure DayOfWeek
go

2

16

Serg Anateus 5 年前

Raiserror (注意单个e)为了避免这种情况,请尝试下面的代码试试这个对我有用

BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;