is possible to do an ''on error goto'' in TSQL?

Question

is possible to do an ''on error goto'' in TSQL?

Hector Angello Silva Carmona

SSCrazy

Points: 2720
More actions
April 12, 2005 at 8:52 pm

#66768

Friends
is possible to do an 'on error goto' in TSQL?

well occurs that one sp of my company has a problem
it is the case for example
create proc SP_BADSP
as
begin
select substring ('aaaa',1,-8)
insert into table1 ('Hello')
end
well, naturally the substring is bad and it throwes the next message of error
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
But for my surprise the insert is done too,
select * from TABLE1
text
--------------------------------------------------
Hello
i dont want that , i want that when appears the error the sp will be stopped, well i know if i used the
special var @@error i could catch the error and then to do a return 1 ,like for example
if @@error <> 0
return 1
but it is very boring to do so it for each instruction.
my question is ..... exists some catch of errors in TSQL like the on error goto ..... of visual basic?
Thanks Brothers....

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

Error trapping is boring whichever way you do it. That's why the majority of people either don't do it at all, or don't do it correctly.

Using your simplistic example, here is a "positive logic" example (ie: we follow a single path determined by the success of the actions, not the failure.)

declare @tbl table (field1 varchar(10))
begin
    declare @err int
    select substring ('aaaa',1,-8)
    set @err = @@ERROR
    If @err = 0
    begin
        insert into @tbl values ('Hello')
        set @err = @@ERROR
        If @err = 0
        begin
            print 'success'
            -- log success or something
        end
        else
        begin
            print 'insert error'
            -- log failure or something
        end
    end
    else
    begin
        print 'substring error'
        -- log failure or something
    end





    select * from @tbl

    
    --return(@err)
    print @err
end

Or you can wait for SQL Server 2005 and use the new Try ... Catch ... block.

--------------------
Colt 45 - the original point and click interface

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

http://www.sommarskog.se/error-handling-II.html

http://www.sommarskog.se/error-handling-I.html

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Also, don't forget that with errorhandling also comes transaction handling!

If you get just a wee bit more complicated than the simple illustration example, then it's very likely that you also want to either rollback or commit actions from/to a certain point. To get this right, and to prevent unexpected 'fall through' in the code, it's very important to understand how SQL Server handles transactions.

BOL is the place of choice to start reading.

/Kenneth