CODEWITHSUNDEEP
sqlsql-servervariablesparameterstry-catch
angel
angel

Reputation: 4632

How to catch the error from a stored procedure? in sql

I have a stored procedure which can return a error this example its going to return error always

create proc regresaerror
as
begin
 raiserror 51001 'Error al borrar los precios especiales' 
end

declare @error varchar(max)
set @error=''
begin try
set @error=exec regresaerror
end try
begin catch
end catch

I tried it (because I need insert this error in a temporary table) but that block code get this error

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'exec'.

Stored procedure couldn't be updated for add a output variable

Then how can I catch the error?

Upvotes: 1

Views: 7109

Answers (1)

Robert
Robert

Reputation: 25753

1st solution:

BEGIN TRY
    EXECUTE regresaerror
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Here is link to MSDN.

2nd solution:

create proc regresaerror 
(
  errmsg varchar(max) out
)
as
begin
 set errmsg =  'Error al borrar los precios especiales' 
 return 51001 -- if error
end 

declare @error varchar(max)
declare @numerror int
set @error=''   
exec @numerror = regresaerror @error out

Upvotes: 1

Related Questions