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) Database