Transaction usage in SP

Question

hi all,

   Actually i fount that in my project all stored procedures following some format. In this format include try...catch and transaction. But i am confused that why we need transaction in all SPs. Try .. catch is Ok and in the catch block i call separate SP to log the error details to another table.

  But is this a good coding i mean using transaction. Actually in the SP there is not multiple nested SPs are called for finishing one task. Only seperate sp is calling in the catch block for logging the error.

The following is my sample sp

CREATE PROC [dbo].[SPNAAME]  

AS  
BEGIN  
SET NOCOUNT ON  
  
DECLARE     
    @ErrorCount as numeric(5)          
      ,@ErrorNumber Int   
      ,@Type varchar(20)  
      ,@LineNumber Int          
      ,@Message varchar(Max)  
      ,@Source nvarchar(50)  
      ,@Module nchar(50)          
      ,@StoredProcedure varchar(100)              
      ,@Method nvarchar(50)        
      ,@UpdatedDate datetime  
      ,@UpdatedBy int  
      ,@UpdatedIP varchar(30)  
        
BEGIN TRY  
  
 BEGIN TRANSACTION  
  
/* here any sql statement */
COMMIT TRANSACTION  
  
END TRY  
  
BEGIN CATCH  
  
 ROLLBACK TRANSACTION  
   
   
  SELECT           
            @ErrorNumber = ERROR_NUMBER(),    
            @LineNumber = ERROR_LINE(),           
            @Message = ERROR_MESSAGE() ,   
            @StoredProcedure=ERROR_PROCEDURE()              
             
            
                      
  EXEC LOGTAABLEINSERTSP @ErrorNumber,@Type, @LineNumber, @Message,NULL,'MODULENAME',@StoredProcedure,'FUNCTIONNAME',NULL,NULL,NULL,NULL   
END CATCH  
  
END

Here transaction is needed or not? This decrease performaance am i right? Multple (multple row insertion or multiple query calling) operations are haandling in the DAL method. So better is handling transaction in .net coding know? Can anyone guid me

Answer 1

Akhilrajau,

Transactions can be used to guarantee consistency in a database while issuing commands that manipulates multiple tables.

It ensure that a set of modifying statements are atomic, namely that either all steps succeed or all steps fail.

Please refer http://blog.sqlauthority.com/2010/06/02/sql-server-stored-procedure-and-transactions/

---

Thanks
Manish

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

yes so only confused so i not use transaction block for such single transactions only try..catch

Answer 3

Hello,

Your thread is not related to SQL Server Data Access, it is why i am moving it to the Transact-SQL Forum where it will be less bad situated.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 4

You don't need to use transactions for the single statement and don't need transaction for SELECT statement. Only if you need to do two or more inserts or two or more changes (updates) you need to use transactions. You can have them with a single statement, but it doesn't do anything and can be the same without.

See also this blog post for the concept of transactions and TRY/CATCH:

How to insert information into multiple related tables and return ID using SQLDataSource

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog