Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
    declare @Result nvarchar(50)
    set @Result = 'OK'
    if @Parm = 1 begin
        set @Result = 'Error Example 1'
        goto ExitProc;
    if @Parm = 2 begin
        set @Result = 'Error Example 2'
        goto ExitProc;
    if @Parm = 3 begin
        set @Result = 'Error Example 3'
        goto ExitProc;
    ect...
    ExitProc:
    select @Result as Result, 100 as P2
    from Table1
                goto is never a good solution. There is much written about this and it has been banned decades ago. classic if..else is the best solution, what is wrong with that ?
– GuidoG
                May 15, 2017 at 9:47
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
    if @Parm = 1 begin
        THROW 60001, 'Error Example 1', 1;
    if @Parm = 2 begin
        THROW 60001, 'Error Example 2', 2;
    if @Parm = 3 begin
        THROW 60001, 'Error Example 3', 3;
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
    begin try
      if @Parm = 1 begin
        THROW 60001, 'Error Example 1', 1;
      if @Parm = 2 begin
        THROW 60001, 'Error Example 2', 2;
      if @Parm = 3 begin
        THROW 60001, 'Error Example 3', 3;
    end try
    begin catch
      select error_message() as Result, 100 as P2
      from Table1
    end catch
                In the catch block, I added: "set @@Result = error_message()". Instead of "set @@Result = '...'" In each if block
– inon
                May 15, 2017 at 10:20
                Yes, much better, because you are also catching any other SQL error that could happen (reference errors, division by 0, ... ...), and you will always have the right description on error_message(). I will modify the answer.
– Marc Guillot
                May 15, 2017 at 10:52
                This is different from the original example, where the select statement is executed even when parm is 1, 2 or 3
– GuidoG
                May 15, 2017 at 11:02
    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'
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.