19. dubna 2012 8:39
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 ENDHere 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
- Přesunutý Papy Normand 26. dubna 2012 17:48 Related to the creation of a stored procedure (From:SQL Server Data Access)
19. dubna 2012 8:46
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 use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
- Navržen jako odpověď Naomi NMicrosoft Community Contributor, Moderator 26. dubna 2012 17:50
20. dubna 2012 1:55yes so only confused so i not use transaction block for such single transactions only try..catch
26. dubna 2012 17:47
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.
26. dubna 2012 17:52Moderátor
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
- Označen jako odpověď akhilrajau 3. května 2012 1:12