Geek asks and answers

Error handling tsqlt when processing an error handler (unit) of a stored procedure

When trying to verify the user GUID in the stored procedure, a simple approach was used; take user input as CHAR (36), then explicitly CAST it as UNIQUEIDENTIFIER in TRY CATCH. CATCH then bubbles up the error with a custom error description using RAISERROR.

Starting the stored procedure manually, everything runs as expected, and an error occurs.

Create a tSQLt test to call the device (a procedure with a GUID check) and process the error with a transaction error that is output and comparable with the expected error; tSQLt detected an error and was processed within tSQLt.

This tells me that the severity of a CAST failure to another data type is handled by tSQLt, and this prevents TRY / CATCH processing in the stored procedure. Like nested procedures, they sometimes ignore TRY / CATCH as part of the child procedure and move on to the parent procedure; for example, if a child proc. refers to a table that does not exist.

Has anyone had a similar problem? Just to test my current line of thinking.

I deleted the test and it was tested elsewhere, but it caused me a “hole” that my DB unit is testing.

Finally, I think I should mention that I know that I can perform another check on the supplied CHAR parameter other than CAST, and throw an error this way, but this is a tSQLt query, not a tSQL query.

EDIT

Code example:

@sGUID CHAR (36) , .

BEGIN TRY
    SELECT CAST(@sGUID AS UNIQUEIDENTIFIER)
END TRY 
BEGIN CATCH
    RAISERROR('Invalid GUID format',16,1)
END CATCH

SELECT , CATCH tSQLt ROLLBACK.

+5
1

RAISEERROR(), , tSQLt, → , , .

, , , RAISEERROR() , RAISERROR(). , .

BEGIN TRY
    SELECT CAST(@sGUID AS UNIQUEIDENTIFIER)
END TRY 
BEGIN CATCH
    EXEC dbo.customprocedure
    --RAISERROR('Invalid GUID format',16,1)
END CATCH
0

More articles:


All Articles