SqlServer GOTO for exit procedure with select

Question

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

Answer 1

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