Re: BEGIN/COMMIT work in Stored Procedure

Fred Prose wrote: > > What is the prevailing thought about using BEGIN/COMMIT WORK within a > Stored Procedure? > > I've got a SP which is called to obtain and increment a "next serial > number" column. This potentially will cause a bottle nexk and I'd > like to have the SP "get in and get out" ASAP. Trolling for an argument, eh? ;-)) I stated my opinion last week about this. To quote Groucho Marx: I'M AGAINST IT!! My opinion (and it is only that) is based on the notion that the execution of a SP is considered a single statement. (In a logged database, if a SP aborts then every action executed by that SP gets rolled back.) Normally one places statements inside transactions, not the other way around. This is a matter of cleanly delineating the entities "statement" and "transaction". By failing to separate these concepts properly, users of the SP will often enter a transaction and then call the SP. hey will then get the error condition "Already in transaction". > Any other suggestions to make sure the SP isn't doing any dirty reads? SET ISOLATION TO COMMITTED READ before calling it. Or have I over-simplified the question? -- -- Jake (Omitting reams of arguments in the interest of brevity) +------------------------------------------------------------+ | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+