July 22, 2014 at 10:43 am
Hi, I have an old proc like below which I want to update to use THROW.
Looks like THROW can save you some lines of code ....
I tried to use it and got some errors, should it be the first command in Catch?
Or existing handling still OK? I'm SQLS 2012
Thanks for help.
M
GO
/*
dbo.ErrorTest 100 -- OK
dbo.ErrorTest 5 -- error
*/
CREATE PROCEDURE dbo.ErrorTest @Amt INT
AS
BEGIN
SET NOCOUNT ON
DECLARE@RowCount INT,
@ProgressMessage VARCHAR(4000) = 'exec alpha.ErrorTest for Amount = ''' + CONVERT(VARCHAR(6), @Amt) + ''''
RAISERROR (@ProgressMessage, 10, 1) WITH NOWAIT -- info only ?
BEGIN TRY
BEGIN TRAN
IF @Amt > 10
BEGIN
SELECT 'Section OK ' AS C1 INTO #temp
SELECT 'Insert is OK'
END
ELSE
BEGIN
SELECT @Amt / 0 --test
END
COMMIT TRAN -- do we really need it here ?
END TRY
BEGIN CATCH
----THROW; -- A: OK here
IF @@TRANCOUNT > 0
ROLLBACK TRAN
---- THROW; -- B: Problem Here
DECLARE @ErrSeverity INT = ERROR_SEVERITY(),
@ErrProc NVARCHAR(MAX) = ERROR_PROCEDURE(),
@ErrLine INT = ERROR_LINE(),
@ErrMsg NVARCHAR(MAX) = ERROR_MESSAGE()
RAISERROR(N'%s (line %d): %s',
@ErrSeverity,
@ErrProc,
@ErrLine,
@ErrMsg )
RETURN
END CATCH
END
July 29, 2014 at 11:31 am
you just need to terminate line before the THROW with a semicolon, then it will work wherever you want to put it. In this case, ROLLBACK TRAN;
July 30, 2014 at 12:07 am
Hi Mario17,
Why do you want to use RAISERROR, when you're using THROW?
Best regards,
Henrik
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply