我使用类似这样的东西:
CREATE PROCEDURE ErrorHandlingPattern
( @intParam int
,@varcharParam varchar(10)
,@dateParam datetime
)
AS
BEGIN TRY
SET NOCOUNT ON
DECLARE @Rows int
,@ErrorMsg varchar(500)
,@LogInfo varchar(5000)
,@TransactionCount int
SELECT @TransactionCount=@@TRANCOUNT
,@LogInfo='@intParam=' +ISNULL(''''+CONVERT(varchar(10), @intParam )+'''','NULL')
+', @varcharParam=' +ISNULL(''''+ @varcharParam +'''','NULL')
+', @dateParam=' +ISNULL(''''+CONVERT(varchar(10), @dateParam,121 )+'''','NULL')
+'; @@TRANCOUNT=' +ISNULL(''''+CONVERT(varchar(10), @@TRANCOUNT )+'''','NULL')
IF @intParam ....
BEGIN
SET @ErrorMsg='Error, invalid value for @intParam: '+ISNULL(''''+CONVERT(varchar(10),@intParam)+'''','NULL')
RAISERROR(@ErrorMsg,16,1)
END
IF @TransactionCount=0
BEGIN
BEGIN TRANSACTION
END
INSERT/UPDATE/DELETE...
SELECT @Rows=@@ROWCOUNT
IF @Rows!=ExpectedValue
BEGIN
SET @ErrorMsg='Error, INSERT/UPDATE/DELETE of tableXYZ resulted in '+ISNULL(''''+CONVERT(varchar(10),@Rows)+'''','NULL')+' rows affected'
RAISERROR(@ErrorMsg,16,1)
END
SET @LogInfo=ISNULL(@LogInfo,'')+'; INSERT/UPDATE/DELETE of tableXYZ resulted in '+ISNULL(''''+CONVERT(varchar(10),@Rows)+'''','NULL')+' rows affected'
IF @TransactionCount=0
BEGIN
COMMIT
END
END TRY
BEGIN CATCH
IF XACT_STATE()!=0
BEGIN
ROLLBACK TRANSACTION
END
DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int
SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)
INSERT INTO YourErrorLog (...) VALUES (...ISNULL(@ErrorMessage,'')+ISNULL(@LogInfo,''))
RETURN 999
END CATCH
RETURN 0
GO
由于您仅仅执行了一批SELECT、INSERT
,所以可以移除CREATE PROCEDURE
和参数声明,并将第一行改为BEGIN TRY
。同时,因为您不是在创建存储过程,所以请将任何RETURN
语句替换为GOTO TheEnd
,并在脚本底部添加一个TheEnd:
标签。