.Net ramblings
# Wednesday, 28 February 2007
SQL Control Flow in stored procedures
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.

Wednesday, 28 February 2007 15:54:09 (GMT Standard Time, UTC+00:00)  #    Comments [0]  Database