CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-server-2014
Chad Baldwin
Chad Baldwin

Reputation: 2602

T-SQL Throw Exception with a Return (not using TRY)

I'm using SQL Server 2014.

EDIT: I guess I should have been more clear. I do understand that by placing everything in a TRY/CATCH, that it will perform how I am wanting it to perform. For this question, I'm mostly just asking if the first example is bad practice, and if there is a way to throw an error that causes termination without the use of RETURN or a TRY/CATCH block. And after looking at the current answers, and linked documentation, I think the answer is...yes it's bad practice, and no you can't terminate with a failure using ONLY the THROW function.

I've written a proc where I would like to have a couple of pre-checks before the remainder of the code runs. An example would be, I have a service that runs for certain customers, and a table controls which customers it should run for, and a different table that controls whether that service itself is enabled.

Here's an example I just threw together. It checks to make sure that service is enabled, and it also makes sure the service is enabled for that specific customer. If not, the proc exits with an error. And also hopefully returning a failure to any job potentially running this proc.

I don't have a lot of experience with THROW or TRY/CATCH, but I just want to make sure I'm not missing something here. I found that THROW does not terminate the proc unless it is in a TRY block. So since I am using THROW outside of a TRY am I correctly using it by also using a RETURN after the THROW?

DECLARE @CustomerID INT = 10000 --Proc Parameter

DECLARE @ServiceID INT = 1

IF NOT EXISTS (SELECT * FROM dbo.Service WHERE ServiceID = @ServiceID AND [Enabled] = 1)
BEGIN
    ;THROW 51000, 'Service is not enabled', 1;
    RETURN;
END
IF NOT EXISTS (SELECT * FROM dbo.CustomerService WHERE CustomerID = @CustomerID AND ServiceID = @ServiceID AND [Enabled] = 1)
BEGIN
    ;THROW 51000, 'Customer does not have service enabled', 1;
    RETURN;
END

--Do stuff

Side Note: I'm really just asking if I'm using THROW incorrectly in this way, or if there is a way to use THROW where it will cause a failure/return.

EDIT/UPDATE: I suppose I could write it like this, not sure if that's an odd way to write SQL though.

DECLARE @CustomerID INT = 10000 --Proc Parameter

DECLARE @ServiceID INT = 1

BEGIN TRY
    IF NOT EXISTS (SELECT * FROM dbo.Service WHERE ServiceID = @ServiceID AND [Enabled] = 1)
        THROW 51000, 'Service is not enabled', 1;

    IF NOT EXISTS (SELECT * FROM dbo.CustomerService WHERE CustomerID = @CustomerID AND ServiceID = @ServiceID AND [Enabled] = 1)
        THROW 51000, 'Customer does not have service enabled', 1;
END TRY
BEGIN CATCH
    THROW;
END CATCH

--Do Stuff

Upvotes: 1

Views: 7798

Answers (4)

C Naylor
C Naylor

Reputation: 31

You can use a throw without a try catch and without a RETURN

DECLARE @CustomerID INT = 10000 --Proc Parameter

DECLARE @ServiceID INT = 1

IF NOT EXISTS (SELECT * FROM dbo.Service WHERE ServiceID = @ServiceID AND [Enabled] = 1)
    THROW 51000, 'Service is not enabled', 1;

IF NOT EXISTS (SELECT * FROM dbo.CustomerService WHERE CustomerID = @CustomerID AND ServiceID = @ServiceID AND [Enabled] = 1)
    THROW 51000, 'Customer does not have service enabled', 1;

--do stuff

Upvotes: 0

J Sidhu
J Sidhu

Reputation: 677

I will recommend using throw exclusively in the context of exception handling & implementation. There are other better ways to perform pre-checks, and you can use output parameters or logs to return and/or log certain checks. check this link

This is from msdn, and it appears that throw will terminate further processing of your query:

" Raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL Server 2017."

If a TRY…CATCH construct is not available, the statement batch is terminated. The line number and procedure where the exception is raised are set. The severity is set to 16."

Upvotes: 1

Dan Guzman
Dan Guzman

Reputation: 46301

A THROW statement terminates the entire batch (unless handled with an outer exception handler) so there is not need for the RETURN after THROW:

DECLARE @CustomerID INT = 10000; --Proc Parameter

DECLARE @ServiceID INT = 1;

BEGIN TRY

    IF NOT EXISTS (SELECT * FROM dbo.Service WHERE ServiceID = @ServiceID AND [Enabled] = 1)
    BEGIN
        THROW 51000, 'Service is not enabled', 1;
    END;
    IF NOT EXISTS (SELECT * FROM dbo.CustomerService WHERE CustomerID = @CustomerID AND ServiceID = @ServiceID AND [Enabled] = 1)
    BEGIN
        THROW 51000, 'Customer does not have service enabled', 1;
    END;
    --do stuff
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK; --this is needed when the TRY block includes a BEGIN TRAN and COMMIT
    THROW;
END CATCH;

Upvotes: 0

Shane Chubbs
Shane Chubbs

Reputation: 11

Since the Return command

Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed. SQL Docs

You could use this method instead of a TRY...CATCH to stop the stored procedure.Using a TRY...CATCH would look like:

BEGIN TRY
DECLARE @CustomerID INT = 10000 --Proc Parameter

DECLARE @ServiceID INT = 1

IF NOT EXISTS (SELECT * FROM dbo.Service WHERE ServiceID = @ServiceID AND [Enabled] = 1)
    THROW 51000, 'Service is not enabled', 1;
IF NOT EXISTS (SELECT * FROM dbo.CustomerService WHERE CustomerID = @CustomerID AND ServiceID = @ServiceID AND [Enabled] = 1)
    THROW 51000, 'Customer does not have service enabled', 1;
--Do stuff
END TRY
BEGIN CATCH
    THROW
END CATCH

Since

If the THROW statement is specified without parameters, it must appear inside a CATCH block. This causes the caught exception to be raised. SQL Docs

Upvotes: 0

Related Questions