SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 TRANSACTION in stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wided
Posting Yak Master

200 Posts

Posted - 02/14/2013 :  11:43:43  Show Profile  Reply with Quote
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

3643 Posts

Posted - 02/14/2013 :  11:55:54  Show Profile  Reply with Quote
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

200 Posts

Posted - 02/15/2013 :  03:09:55  Show Profile  Reply with Quote
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

200 Posts

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

wided
Posting Yak Master

200 Posts

Posted - 02/15/2013 :  04:45:56  Show Profile  Reply with Quote
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

200 Posts

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

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 02/15/2013 :  06:45:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3643 Posts

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

wided
Posting Yak Master

200 Posts

Posted - 02/15/2013 :  10:19:44  Show Profile  Reply with Quote
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

200 Posts

Posted - 02/15/2013 :  11:40:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 02/18/2013 :  01:04:37  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000