CODEWITHSUNDEEP
sql-serverstored-procedures
inon
inon

Reputation: 1772

SqlServer GOTO for exit procedure with select

I know that have many decisions about it. Yet. If I have procedure then in many cases, I want to select a result and exit the procedure. It good to use GOTO statement, or have a better way (not classic if...else)

Example:

create procedure MyProc @Parm int
as
    declare @Result nvarchar(50)

    set @Result = 'OK'

    if @Parm = 1 begin
        set @Result = 'Error Example 1'
        goto ExitProc;
    end

    if @Parm = 2 begin
        set @Result = 'Error Example 2'
        goto ExitProc;
    end

    if @Parm = 3 begin
        set @Result = 'Error Example 3'
        goto ExitProc;
    end

    ect...

    ExitProc:

    select @Result as Result, 100 as P2
    from Table1

Upvotes: 3

Views: 3141

Answers (2)

Marc Guillot
Marc Guillot

Reputation: 6465

Whit your real code being more complex than a single if else if ... structure (as said on a comment), then you could raise your own exceptions whenever you need them, forcing the stored procedure to exit and informing your application of the error.

Example :

create procedure MyProc @Parm int
as
    if @Parm = 1 begin
        THROW 60001, 'Error Example 1', 1;
    end

    if @Parm = 2 begin
        THROW 60001, 'Error Example 2', 2;
    end

    if @Parm = 3 begin
        THROW 60001, 'Error Example 3', 3;
    end

    ...

Now your application can catch these exceptions thrown by SQL Server as if they were any other SQL error.

You could even catch and handle these errors on the stored procedure itself, although I think that catching them on your application is more elegant.

Example of catching the errors on the stored procedure :

create procedure MyProc @Parm int
as

    begin try
      if @Parm = 1 begin
        THROW 60001, 'Error Example 1', 1;
      end

      if @Parm = 2 begin
        THROW 60001, 'Error Example 2', 2;
      end

      if @Parm = 3 begin
        THROW 60001, 'Error Example 3', 3;
      end

      ...
    end try

    begin catch
      select error_message() as Result, 100 as P2
      from Table1
    end catch

Upvotes: 3

zapping
zapping

Reputation: 4116

You can use CASE instead of the GOTO. 

CREATE PROCEDURE MyProc @Parm int AS
    DECLARE @Result nvarchar(50)

    SELECT 100 as P2, @Result = CASE @Parm
                         WHEN 1 THEN 'Error Example 1'
                         WHEN 2 THEN 'Error Example 2'
                         WHEN 2 THEN 'Error Example 3'
                         ELSE 'OK'
                     END

Upvotes: 0

Related Questions