I am not an expert on SQl Server. Is this a valid pattern for handling errors in a batch of SELECT, INSERT...in SQl SERVER ? (I use v.2008)
BEGIN TRANSACTION
BEGIN TRY
-- statement 1
-- statement 2
-- statement 3
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
Thanks
I use something like this:
CREATE PROCEDURE ErrorHandlingPattern
( @intParam int
,@varcharParam varchar(10)
,@dateParam datetime
)
AS
BEGIN TRY
SET NOCOUNT ON
DECLARE @Rows int --store @@ROWCOUNT in this
,@ErrorMsg varchar(500) --temp string to build the contents of messages passed into RAISERROR calls
,@LogInfo varchar(5000) --will hold any info necessary for error debugging, append to this throughout the procedure with important info
,@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')
--validate parameters
IF @intParam ....
BEGIN --logical error
SET @ErrorMsg='Error, invalid value for @intParam: '+ISNULL(''''+CONVERT(varchar(10),@intParam)+'''','NULL')
RAISERROR(@ErrorMsg,16,1) --send control to the BEGIN CATCH block
END
IF @TransactionCount=0 --if we are already in a transaction, no need to start another, nesting transactions +rollback=warnings about transaction count not being the same as when the procedure started.
BEGIN
BEGIN TRANSACTION
END
--do your work here....
INSERT/UPDATE/DELETE...
SELECT @Rows=@@ROWCOUNT
IF @Rows!=ExpectedValue
BEGIN --logical error
SET @ErrorMsg='Error, INSERT/UPDATE/DELETE of tableXYZ resulted in '+ISNULL(''''+CONVERT(varchar(10),@Rows)+'''','NULL')+' rows affected'
RAISERROR(@ErrorMsg,16,1) --send control to the BEGIN CATCH block
END
--append improtant info to log string
SET @LogInfo=ISNULL(@LogInfo,'')+'; INSERT/UPDATE/DELETE of tableXYZ resulted in '+ISNULL(''''+CONVERT(varchar(10),@Rows)+'''','NULL')+' rows affected'
IF @TransactionCount=0 --only end the transaction if it started here
BEGIN
COMMIT --put in try block to be able to catch any problems committing
END
END TRY
BEGIN CATCH
IF XACT_STATE()!=0 --if there is any error end the transaction ASAP
BEGIN
ROLLBACK TRANSACTION
END
--will echo back the complete original error message
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)
--because the transaction was ROLLBACKed this insert will be recorded in the database
INSERT INTO YourErrorLog (...) VALUES (...ISNULL(@ErrorMessage,'')+ISNULL(@LogInfo,''))
RETURN 999
END CATCH
RETURN 0
GO
Since you are just doing a batch of a batch of SELECT, INSERT, you can just remove the CREATE PROCEDURE and parameter declarations and have the first line start at BEGIN TRY. Also, because you are not creating a procedure, replace any RETURN statements with GOTO TheEnd and add a TheEnd: label at the script's bottom.
Nearly:
BEGIN TRANSACTION;
BEGIN TRY
-- Generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
This was taken from the MSDN documentation on TRY/CATCH where other examples can be found: http://msdn.microsoft.com/en-us/library/ms175976.aspx
BEGIN TRANSACTION and COMMIT within the TRY block. Would you NOT want to catch a problem creating or committing the transaction?