Tim Mackey's Weblog

coming from the c# world of strict control flow and built-in bail-out for unhandled exceptions, i was caught out by something in SQL 2005 recently.
many of my stored procedures take this form:

    update sometable set whatever = @whatever
    exec SP_History 'joe bloggs', 'order taken', '€500'

i was assuming that if the update statement failed, the subsequent SP_History SP would not be executed.  however i was wrong, and i now have the following code where i want the SP to stop executing if something goes wrong:

if @@ERROR <> 0 
    RETURN @@ERROR    -- bail out

the exception is still caught by .net, any handling code you may have will be unaffected there by the return statement.