Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 TRANSACTION in stored procedure

Author  Topic 

wided
Posting Yak Master

218 Posts

Posted - 2013-02-14 : 11:43:43
Hello

I have a stored procedure that makes a series of requests UPDATE:

example:
update table1
set col1 = x
Where y = 1

update table2
set col1 = z
Where w
etx .....

I need to include transaction if there is a problem in the second query, I cancel the update of the first request

Thank you for helping me

I tried BEGIN TRANSACTION COMMIT TRANSACTION go to error ...? Hello

I have a stored procedure that makes a series of requests UPDATE:

example:
update table1
set col1 = x
Where y = 1

update table2
set col1 = z
Where w
etx .....

I need to include transaction if there is a problem in the second query, I cancel the update of the first request

Thank you for helping me

I tried BEGIN TRANSACTION COMMIT TRANSACTION go to error ... But it does not work
I think I do not know the syntax

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-14 : 11:55:54
Take a look at the example "B. Using TRY…CATCH in a transaction" on the MSDN page (link below). You should follow that pattern.

http://msdn.microsoft.com/en-us/library/ms175976.aspx
Go to Top of Page

wided
Posting Yak Master

218 Posts

Posted - 2013-02-15 : 03:09:55
thank you James K

the example that you gave me returns the error but does not cancel the transaction
What should we add
Go to Top of Page

wided
Posting Yak Master

218 Posts

Posted - 2013-02-15 : 04:34:46
my queries (UPDATE) are in a loop
(4 UPDATE)
Go to Top of Page

wided
Posting Yak Master

218 Posts

Posted - 2013-02-15 : 04:45:56
i have this message when i include begin transaction et commit transaction

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count =0, current count = 1.
Go to Top of Page

wided
Posting Yak Master

218 Posts

Posted - 2013-02-15 : 05:55:10
all the examples in the forums using a single query, my example requires at least 4 queries
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-15 : 06:45:50
This link may help you
http://stackoverflow.com/questions/4032510/how-to-rollback-if-any-update-is-not-success

--
Chandu
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-15 : 06:59:58
Can you post your queries as they exist now?
Go to Top of Page

wided
Posting Yak Master

218 Posts

Posted - 2013-02-15 : 10:19:44
I do try and catch four, I tried with a single

create procedure [dbo].[MYPROC] @pSessionUserID int,
@pSucces bit output,@NbMatriculeTraite int output,
@pMsgErreur VARCHAR(100) output


as
Set NOCount on


DECLARE @STATUS INT, @Matricule VARCHAR(10),@wUserID int,@wEmployeID int,@WdateSysteme datetime
,@wOnSessionGlobaleCloture bit,@wOnSessionUserCloture bit,
@DateDebutSession DATETIME,@DateFinSession DATETIME, @StartTime datetime, @EndTime datetime,
@wSiteID int , @wSessionTimeID int , @wMatriculeUser varchar(10),@TempsPasse int ,@MoyenParMatricule int,
@wNbMatriculeTraite int, @wSucces bit, @wMsgErreur VARCHAR(100)

begin

BEGIN TRANSACTION



DECLARE Icurseur CURSOR FOR
SELECT Matricule FROM dbo.H11FT_CollectionEmploye(@wSiteID, null,null,
'', 'z') ORDER BY Matricule

OPEN Icurseur
SELECT @Status=0
-- Boucle matricule--------------------------------
WHILE @Status=0
BEGIN
FETCH NEXT FROM Icurseur INTO @Matricule
SELECT @Status = @@fetch_status
IF @Status <> 0 BREAK

-- MàJ Planning --------FIRST UPDATE-------------------------
BEGIN TRY
update dbo.H51_PlanningEmploye
set OnSessionCloture = 1, DateCloture=@WdateSysteme, CloturerPar=@wMatriculeUser
where Matricule= @Matricule and
DateJournee between @DateDebutSession and @DateFinSession
END TRY
BEGIN CATCH
---- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;
IF @@TRANCOUNT > 0
BEGIN
COMMIT TRANSACTION;
break
END
END CATCH;
-- MàJ Mvt Pointage ------- update 2--------------

BEGIN TRY
update H52_MvtPointage
Set OnCloture=1, DateCloture=@WdateSysteme,CloturePar=@wMatriculeUser
where Matricule= @Matricule and
JourCptPnt between @DateDebutSession and @DateFinSession;
END TRY

BEGIN CATCH
---- Execute error retrieval routine.
SELECT ERROR_MESSAGE() AS ErrorMessage;
COMMIT TRANSACTION;
BREAK

END CATCH;
-- MàJ Mvt Absence ------------update 3-------------
BEGIN TRY
update dbo.H42_MvtAbsenceDetail
Set OnCloture=1, DateCloture=@WdateSysteme,CloturePar=@wMatriculeUser
where Matricule= @Matricule and
JourCpt between @DateDebutSession and @DateFinSession
END TRY
BEGIN CATCH
---- Execute error retrieval routine.
SELECT ERROR_MESSAGE() AS ErrorMessage;
COMMIT TRANSACTION;
BREAK

END CATCH;

-- MàJ MvtPresence ----------update 4----------------
BEGIN TRY
update dbo.H53_MvtPresence
set OnCloture = 1, DateCloture=@WdateSysteme, CloturePar=@wMatriculeUser
where Matricule= @Matricule and
DateMvt between @DateDebutSession and @DateFinSession

END TRY
BEGIN CATCH
---- Execute error retrieval routine.
SELECT ERROR_MESSAGE() AS ErrorMessage;
COMMIT TRANSACTION;
BREAK

END CATCH;
end -- While : Boucle matricule --------------------
--
Set @pSucces=1
Close Icurseur
Deallocate Icurseur
COMMIT TRANSACTION
End

Go to Top of Page

wided
Posting Yak Master

218 Posts

Posted - 2013-02-15 : 11:40:44
thank you Bandi

I tried the example you gave me, but the statement "IF @ @ ROWCOUNT = 0 ROLLBACK TRAN" I do not because should update query may return 0 because data exist.

by which I could replace this instruction especially in the case of a judgment of a nonexistent column or table name wrong

I also expected response James
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-18 : 01:04:37
Welcome..

You can check the following link for the use of @@TRANCOUNT along with ROLLBACK/COMMIT
http://msdn.microsoft.com/en-us/library/ms187967.aspx

--
Chandu
Go to Top of Page
   

- Advertisement -