March 22, 2012 at 9:22 am
...
I'd say it gives you a standard way of writing the code with explicit control over the commit/rollback.
When I see BEGIN TRANSACTION, it give the idea that the following multiple changes should be taken as atomic transaction.
Single change statement is atomic transaction itself and requires no additional control, therefore, when I see only one change statement between BEGIN TRAN - COMMIT, I have a feeling that the person who used has no idea what transaction is...
What about stored proc, which will not make any changes but uses few queries, (eg. using temp tables, which will be populated) to just select data? Will developer take suggested template and wrap his selects into single transaction?
I do like standard code a lot and I'm quite nasty when devs do not adhere to chosen formats/templates. Error handling part of it (except transaction status check and roll-back) is a good candidate to be used as template. But, using transaction control where it's not required, in my opinion, does not represent good standard.
As, I've said before, transaction control inside of stored procedures is not the best design anyway (until it's independently used proc).
March 22, 2012 at 9:58 am
Eugene Elutin (3/22/2012)
...
I'd say it gives you a standard way of writing the code with explicit control over the commit/rollback.
When I see BEGIN TRANSACTION, it give the idea that the following multiple changes should be taken as atomic transaction.
Single change statement is atomic transaction itself and requires no additional control, therefore, when I see only one change statement between BEGIN TRAN - COMMIT, I have a feeling that the person who used has no idea what transaction is...
What about stored proc, which will not make any changes but uses few queries, (eg. using temp tables, which will be populated) to just select data? Will developer take suggested template and wrap his selects into single transaction?
I do like standard code a lot and I'm quite nasty when devs do not adhere to chosen formats/templates. Error handling part of it (except transaction status check and roll-back) is a good candidate to be used as template. But, using transaction control where it's not required, in my opinion, does not represent good standard.
As, I've said before, transaction control inside of stored procedures is not the best design anyway (until it's independently used proc).
I guess that depends on where you are controlling the transactions.
If it is handled in the application code, then you don't want the commit/rollback in the stored procedure.
If the application is not controlling the transaction and simply calling the stored procedure, then yes you do want to have the commit/rollback in the stored procedure.
It all depends on the application/environment. I did database development where the sometimes the application handled the transaction (made several calls to the database running several stored procedures) and other times it didn't relying on the database to control the transaction. And before you criticize this environment, the team I was on inherited it from the subcontractor that originally started the project. We didn't have the time or resources to change it.
March 22, 2012 at 10:38 am
...
It all depends on the application/environment. I did database development where the sometimes the application handled the transaction (made several calls to the database running several stored procedures) and other times it didn't relying on the database to control the transaction. And before you criticize this environment, the team I was on inherited it from the subcontractor that originally started the project. We didn't have the time or resources to change it.
I'm not going to criticise, as I have a great experience in inheriting different sort of code (no-one left me a chest of gold as yet :hehe:)
March 22, 2012 at 10:57 am
Eugene Elutin (3/22/2012)
...
It all depends on the application/environment. I did database development where the sometimes the application handled the transaction (made several calls to the database running several stored procedures) and other times it didn't relying on the database to control the transaction. And before you criticize this environment, the team I was on inherited it from the subcontractor that originally started the project. We didn't have the time or resources to change it.
I'm not going to criticise, as I have a great experience in inheriting different sort of code (no-one left me a chest of gold as yet :hehe:)
You might not, but we know that there are those out there that do and on a regular basis. :w00t:
March 23, 2012 at 12:08 am
CREATE PROCEDURE dbo.Insert_Sp ( @param ,@param1 output)
AS
SET NOCOUNT ON
BEGIN TRY
Insert Statement..............
END TRY
BEGIN CATCH
Select @param1= @@error
RETURN 1 -- fail
END CATCH
RETURN 0 --success
Will @param1 return error code in this case or will it reset?? and does the abouve code look fine??
March 23, 2012 at 1:18 am
I would stick closer to the example Lowell gave you, (which is almost identical to a template I use) .
The important distinction in that technique compared with what you just posted is that errors are being reported via raiserror, and not return parameters.
Using a return parameter for errors mean it can get confusing when you need "real" values returned, as you have to decide in the caller if the return value indicates success, failure or something else. It also means of course that any inbuilt error handling in the client no longer works as it will not see your return paremeter as indicating an error.
Mike
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply