SqlServer GOTO for exit procedure with select
I know I have many solutions. Yet. If I have a procedure, then in many cases I want to select the result and exit the procedure. Use instruction well GOTO
or have a better way (not the classic one 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
source to share
If your real code is more complex than the if else if ... syntax (as said in the comment), then you can create your own exceptions when you need it, forcing the stored procedure to exit and inform your application of an 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 can even catch and handle these errors in the stored procedure itself, although I think tricking them into your application is more elegant.
An example of catching errors in a 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
source to share