我需要经常使用TRY..CATCH
块执行存储过程。 因此,我编写了一个包含TRY..CATCH
块+输入参数的存储过程:
CREATE PROCEDURE [dbo].[exec_sp_with_try_catch]
(@pnvcSPName NVARCHAR(100))
AS
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
BEGIN
BEGIN TRY
exec @pnvcSPName;
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
END CATCH
END;
然后,如果我想用try...catch
块执行存储过程,我可以直接使用上面的存储过程:
exec [dbo].[exec_sp_with_try_catch] @pnvcSPName = '[dbo].[another_sp]';
但是,上述方法仅在我们不在[dbo].[another_sp]
中使用任何参数时才有效。
如果我们在[dbo].[another_sp]
中使用参数:
exec [dbo].[exec_sp_with_try_catch] @pnvcSPName = '[dbo].[another_sp] @para = ''K''';
它会引发以下错误:
Msg 50000,级别 16,状态 2,过程 exec_sp_with_try_catch,第 30 行
名称"[dbo].[another_sp] @para = 'K'' 不是有效的标识符。
为什么,以及如何纠正它?
试试这个:
CREATE PROCEDURE [dbo].[exec_sp_with_try_catch]
@pnvcSPName NVARCHAR(100) ,
@para NVARCHAR(100)
AS
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
BEGIN
BEGIN TRY
exec @pnvcSPName @para;
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
END CATCH
END;
exec dbo.exec_sp_with_try_catch '[dbo].[another_sp]','K';
这样做
CREATE PROCEDURE [dbo].[exec_sp_with_try_catch] (@pnvcSPName NVARCHAR(100))
AS
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
BEGIN
BEGIN TRY
EXEC sys.sp_executesql @pnvcSPName; --pay attention on possible need of escaping ' char here
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
END CATCH
END;