| Author |
 Topic  |
|
|
wided
Posting Yak Master
152 Posts |
 Posted - 02/14/2013 : 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
Flowing Fount of Yak Knowledge
1515 Posts |
|
|
wided
Posting Yak Master
152 Posts |
Posted - 02/15/2013 : 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 |
 |
|
|
wided
Posting Yak Master
152 Posts |
Posted - 02/15/2013 : 04:34:46
|
my queries (UPDATE) are in a loop
(4 UPDATE) |
|
|
|
wided
Posting Yak Master
152 Posts |
Posted - 02/15/2013 : 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. |
|
|
|
wided
Posting Yak Master
152 Posts |
Posted - 02/15/2013 : 05:55:10
|
| all the examples in the forums using a single query, my example requires at least 4 queries |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1442 Posts |
|
|
James K
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 02/15/2013 : 06:59:58
|
| Can you post your queries as they exist now? |
|
|
|
wided
Posting Yak Master
152 Posts |
Posted - 02/15/2013 : 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
|
|
|
|
wided
Posting Yak Master
152 Posts |
Posted - 02/15/2013 : 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 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1442 Posts |
|
| |
Topic |
|
TRANSACTION in stored procedure
