你好,我正在编写一个存储过程,它将根据每小时的计划执行一批作业,我正在尝试决定是返回错误还是引发错误。假设我会在每个作业内记录错误,这会导致更好的性能和可维护性?
例如:
--使用错误代码
CREATE PROCEDURE Job1
AS
BEGIN
BEGIN TRY
--Do some work
END TRY
BEGIN CATCH
--log error
RETURN 1; --Return 1 for error
END CATCH
RETURN 0;
END
CREATE PROCEDURE USP_BatchJob
AS
BEGIN
BEGIN TRANSACTION;
DECLARE @Error INT;
EXEC @Error = Job1;
IF @Error <> 0
GOTO ErrorHandling;
EXEC @Error = Job1;
IF @Error <> 0
GOTO ErrorHandling;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
RETURN;
ErrorHandling:
IF @@TRANCOUNT > 0
ROLLBACK;
END
例如:RaiseError
CREATE PROCEDURE Job1
AS
BEGIN
BEGIN TRY
--Do some work
END TRY
BEGIN CATCH
--log error
RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE());
END CATCH
END
CREATE PROCEDURE USP_BatchJob
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
EXEC Job1;
EXEC Job1;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK;
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END
后者似乎能够产生更易维护的代码。