January 18, 2018 at 3:02 am
We have a stored procedure as below, which inserts a record, then checks @@ROWCOUNT to see if it was successful and throw an error if it returned 0.
Is the THROW statement unreachable? I cannot think of any situation where it would run.
I'm not sure how to prove it, but I have tried:
1) Altering the datatype of one fldProductID in the table from BIGINT to TINYINT, and then passing 9999999 in, which results in an arethmetic overflow error. The THROW statement does not trigger.
2) Altering fldActivityNote so that it does not allow NULLS, then trying to pass in a null value, which results in an error. Again the THROW statement does not trigger.
ALTER PROCEDURE [dbo].[spiACTIVITY_ADD] (@ProductID INT, @InventoryID BIGINT, @LocationID BIGINT, @ActivityTypeCode NVARCHAR(100), @ActivityNote NVARCHAR(255), @FromQty BIGINT, @ToQty BIGINT)
AS
BEGIN
BEGIN TRY
-- Declarations
DECLARE @ActivityTypeID INT
SET @ActivityTypeID = 1
-- Add Inventory Created Activity
INSERT INTO tblActivity
(
fldActivityTypeID,
fldProductID,
fldInventoryID,
fldLocationID,
fldFromQty,
fldToQty,
fldActivityDateTime,
fldActivityNote,
fldActivitySuccess
)
VALUES
(
@ActivityTypeID,
@ProductID,
@InventoryID,
@LocationID,
@FromQty,
@ToQty,
GETDATE(),
@ActivityNote,
1
)
-- Raise error if no record added to the activity table
IF (@@ROWCOUNT = 0)
BEGIN
SET @ERROR_MESSAGE = '*** is this THROW code unreachable if the INSERT above fails? ***';
;THROW 60014, @ERROR_MESSAGE, 10
END
END TRY
BEGIN CATCH
;THROW
END CATCH
END
January 18, 2018 at 3:43 am
Check the "remarks" section and "Errors Unaffected by a TRY…CATCH Construct" in the try-catch documentation : https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 18, 2018 at 3:59 am
Which THROW statement are you referring to? Strikes me that if there's an error, such as an arithmetic overflow, then control will immediately be passed to the CATCH block and so the second THROW will be triggered. If, however, there were no error but no rows were inserted into your table (which doesn't look possible given that you're inserting a single row consisting of a number of variables) then the first THROW would be invoked instead.
John
January 18, 2018 at 5:21 am
^^ I agree with John.
Note that there is no need to precede a THROW statement with a semicolon.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 29, 2018 at 3:09 am
John Mitchell-245523 - Thursday, January 18, 2018 3:59 AMWhich THROW statement are you referring to? Strikes me that if there's an error, such as an arithmetic overflow, then control will immediately be passed to the CATCH block and so the second THROW will be triggered. If, however, there were no error but no rows were inserted into your table (which doesn't look possible given that you're inserting a single row consisting of a number of variables) then the first THROW would be invoked instead.John
Hi John. I am referring to the first THROW statement (not the one in the BEGIN CATCH section). Looking at the documentation Johan refers to it does appear that processing is transferred to the CATCH statement when an error is encountered within a TRY block.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply