how can write this stored procedure, so that would not hide the error

Question

At the moment I write stored procedures this way:

create proc doStuff
@amount int
as
begin try
begin tran
...
if something begin  select 'not_good' rollback return end
if someelse begin  select 'some_other_thing' rollback return end
--do the stuff
...
commit 
end try
begin catch 
if @@trancount > 0 rollback
select 'error'
end catch

the problem with this approach is that I hide the error, anybody knows to do this some other ?

Answer 1

What database are you using? In SQL Server you can use the keyword RASIERROR to generate error messages. See RAISERROR (Transact-SQL)

Answer 2

Assuming SQL Server here, since that looks a lot like SQL Server syntax:

Preferably, you should also use SAVE TRAN so you can treat the procedure as its own unit of work and let the caller choose whether or not to rollback the entire transaction (as opposed to only rolling back the work in this particular block). Remus Rusanu wrote an article about that a while back.

Putting that aside for the moment, you need to save the error immediately after you catch it and then re-raise it after rolling back (normally with some additional info):

CREATE PROCEDURE xyz [params]
AS
BEGIN
    BEGIN TRY
        BEGIN TRAN
        -- Do the work
        COMMIT
    END TRY
    BEGIN CATCH
        DECLARE
            @Error int,
            @Severity int,
            @Message varchar(4000)
        SELECT
            @Error = ERROR_NUMBER(),
            @Severity = ERROR_SEVERITY(),
            @Message = ERROR_MESSAGE()
        ROLLBACK
        RAISERROR('Procedure xyz: %d: %s', @Severity, 1, @Error, @Message)
    END CATCH
END

Answer 3

SQL server sp.

create procedure doStuff
(
   @amount int
) 
as
begin try
   begin transaction

    if something
        raiserror('not_good', 16, 1)

    if somethingelse
        raiserror('some_other_thing', 16, 1)

    do stuff here

   commit
end try
begin catch
    if @@trancount > 0
        rollback

    declare @errmsg nvarchar(4000), @errseverity int
    select @errmsg = error_message(), @errseverity = error_severity()

    raiserror(@errmsg, @errseverity, 1)
end catch