I have a modest stored procedure that creates a temporary table, populates it, then clears a permanent table and copies everything from the temporary table into the permanent table. This series of steps is performed three times for three different tables, and the whole thing takes only a few lines.

I have no particular reason to expect that any part of this will fail, but in the case of the third table, there is also a call to a different stored procedure that does the same thing for a fourth table, except that this fourth table is much more likely to fail than the other three. Failure is unlikely for any of them, though.

What I was wondering was whether there was any advantage to break the small, but monolithic, procedure up into different batches, like so:

BEGIN
DO SOMETHING WITH TABLE 1
END
GO

BEGIN
DO SOMETHING WITH TABLE 2
END
GO

etc.

What I am specifically thinking about is a way to allow the processing of tables 1 and 2 to work even if the processing of table 3 does not (if 3 fails then four can also fail...or not, I don't really care which)?

I have no experience with stored procedure behavior, so I'm wondering whether a failure of one batch will terminate the whole stored procedure, or whether it will proceed to the next batch?