Author |
Topic |
wided
Posting Yak Master
218 Posts |
Posted - 2013-02-14 : 11:43:43
|
HelloI have a stored procedure that makes a series of requests UPDATE:example:update table1set col1 = xWhere y = 1update table2set col1 = zWhere wetx .....I need to include transaction if there is a problem in the second query, I cancel the update of the first requestThank you for helping meI tried BEGIN TRANSACTION COMMIT TRANSACTION go to error ...? HelloI have a stored procedure that makes a series of requests UPDATE:example:update table1set col1 = xWhere y = 1update table2set col1 = zWhere wetx .....I need to include transaction if there is a problem in the second query, I cancel the update of the first requestThank you for helping meI tried BEGIN TRANSACTION COMMIT TRANSACTION go to error ... But it does not workI 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 |
|
|
wided
Posting Yak Master
218 Posts |
Posted - 2013-02-15 : 03:09:55
|
thank you James Kthe example that you gave me returns the error but does not cancel the transactionWhat should we add |
|
|
wided
Posting Yak Master
218 Posts |
Posted - 2013-02-15 : 04:34:46
|
my queries (UPDATE) are in a loop(4 UPDATE) |
|
|
wided
Posting Yak Master
218 Posts |
Posted - 2013-02-15 : 04:45:56
|
i have this message when i include begin transaction et commit transactionTransaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count =0, current count = 1. |
|
|
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 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
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? |
|
|
wided
Posting Yak Master
218 Posts |
Posted - 2013-02-15 : 10:19:44
|
I do try and catch four, I tried with a singlecreate procedure [dbo].[MYPROC] @pSessionUserID int, @pSucces bit output,@NbMatriculeTraite int output, @pMsgErreur VARCHAR(100) output asSet NOCount onDECLARE @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)beginBEGIN 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 TRANSACTIONEnd |
|
|
wided
Posting Yak Master
218 Posts |
Posted - 2013-02-15 : 11:40:44
|
thank you BandiI 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 wrongI also expected response James |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
|