August 9, 2012 at 9:58 am
Hi,
I have wrote the next procedure
USE [RECLACSR-DB]
GO
/****** Object: StoredProcedure [dbo].[InfoPrComplement] Script Date: 08/09/2012 15:00:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[InfoPrComplement]
-- Add the parameters for the stored procedure here
@node nchar (10),
@resultat nvarchar (100) ='Paiement Complet' output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
begin transaction
----Verifier la valeur du titre
begin Try
Declare @Valeurtitre int;
set @Valeurtitre = (select PAR_CATEGORIE.CAT_MONTANT
from CSR_DOSSIER,PAR_CATEGORIE
where CSR_DOSSIER.CAT_CODE = PAR_CATEGORIE.CAT_CODE
and CSR_DOSSIER.DOS_NODE = @node);
Declare @Montantverser int;
set @Montantverser = (Select SUM (dbo.CSR_COMPLEMENT.CMP_MTVERSE)
From dbo.CSR_COMPLEMENT WHERE dbo .CSR_COMPLEMENT.DOS_ID =(SELECT dbo.CSR_DOSSIER.DOS_ID
FROM dbo.CSR_DOSSIER
WHERE DOS_NODE =@node ));
if (@Montantverser = @Valeurtitre )
begin
-----------resultat "Paiement complet"
set @resultat = 'Paiement complet';
end
else
begin
SELECT dbo.CSR_DOSSIER.DOS_NOM +' '+ ISNULL ( dbo .CSR_DOSSIER.DOS_PRENOM, ' ') as Nom,
CSR_DOSSIER.PID_CODE +' - '+PAR_PIDENT.PID_LIB AS Poste , CSR_DOSSIER.TIT_CODE +' - '+ PAR_TITRE.TIT_LIB AS Titre ,
CSR_DOSSIER.CAT_CODE, dbo.PAR_CATEGORIE.CAT_MONTANT,
(Select SUM (dbo.CSR_COMPLEMENT.CMP_MTVERSE)
From dbo.CSR_COMPLEMENT WHERE dbo .CSR_COMPLEMENT.DOS_ID =(SELECT dbo.CSR_DOSSIER.DOS_ID
FROM dbo.CSR_DOSSIER
WHERE DOS_NODE =@node )) as MontantVerse
FROM dbo.CSR_DOSSIER ,dbo.PAR_CATEGORIE ,dbo.PAR_PIDENT, PAR_TITRE
WHERE dbo .CSR_DOSSIER.DOS_ID =(SELECT dbo.CSR_DOSSIER.DOS_ID
FROM dbo.CSR_DOSSIER WHERE DOS_NODE =@node )
AND CSR_DOSSIER.CAT_CODE = PAR_CATEGORIE .CAT_CODE
AND CSR_DOSSIER.PID_CODE = PAR_PIDENT.PID_CODE
AND CSR_DOSSIER.TIT_CODE = PAR_TITRE.TIT_CODE;
set @resultat ='Paiement Partiel';
end
return @resultat
end try
begin catch
SELECT ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage;
-----------IF @@TRANCOUNT > 0ROLLBACK TRANSACTION;
end catch
END
it's complie well, but when i execute , althought i have teh result, ialso got thesse error
Msg 266, Niveau 16, État 2, Procédure InfoPrComplement, Ligne 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 7, current count = 8.
(1 ligne(s) affectée(s))
(1 ligne(s) affectée(s))
Msg 3998, Niveau 16, État 1, Ligne 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
And I don't know why.
My return value also gives this message
ErrorProcedureErrorLineErrorMessage
InfoPrComplement53Conversion failed when converting the nvarchar value 'Paiement Partiel' to data type int.
@resultat
NULL
Return Value
-6
thanks for your help
August 9, 2012 at 10:19 am
I see the begin transaction, but you seem to be missing the commit/rollback statements. Put the begin/commit transaction statements inside the TRY block. Your CATCH block should test the transaction state and rollback if necessary. Take a look at the example on this page: http://msdn.microsoft.com/en-us/library/ms179296%28v=sql.105%29.aspx
August 9, 2012 at 12:22 pm
USE [RECLACSR-DB]
GO
/****** Object: StoredProcedure [dbo].[InfoPrComplement] Script Date: 08/09/2012 15:00:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[InfoPrComplement]
-- Add the parameters for the stored procedure here
@node nchar (10),
@resultat nvarchar (100) ='Paiement Complet' output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
begin transaction
----Verifier la valeur du titre
begin Try
Declare @Valeurtitre int;
set @Valeurtitre = (select PAR_CATEGORIE.CAT_MONTANT
from CSR_DOSSIER,PAR_CATEGORIE
where CSR_DOSSIER.CAT_CODE = PAR_CATEGORIE.CAT_CODE
and CSR_DOSSIER.DOS_NODE = @node);
Declare @Montantverser int;
set @Montantverser = (Select SUM (dbo.CSR_COMPLEMENT.CMP_MTVERSE)
From dbo.CSR_COMPLEMENT WHERE dbo .CSR_COMPLEMENT.DOS_ID =(SELECT dbo.CSR_DOSSIER.DOS_ID
FROM dbo.CSR_DOSSIER
WHERE DOS_NODE =@node ));
if (@Montantverser = @Valeurtitre )
begin
-----------resultat "Paiement complet"
set @resultat = 'Paiement complet';
end
else
begin
SELECT dbo.CSR_DOSSIER.DOS_NOM +' '+ ISNULL ( dbo .CSR_DOSSIER.DOS_PRENOM, ' ') as Nom,
CSR_DOSSIER.PID_CODE +' - '+PAR_PIDENT.PID_LIB AS Poste , CSR_DOSSIER.TIT_CODE +' - '+ PAR_TITRE.TIT_LIB AS Titre ,
CSR_DOSSIER.CAT_CODE, dbo.PAR_CATEGORIE.CAT_MONTANT,
(Select SUM (dbo.CSR_COMPLEMENT.CMP_MTVERSE)
From dbo.CSR_COMPLEMENT WHERE dbo .CSR_COMPLEMENT.DOS_ID =(SELECT dbo.CSR_DOSSIER.DOS_ID
FROM dbo.CSR_DOSSIER
WHERE DOS_NODE =@node )) as MontantVerse
FROM dbo.CSR_DOSSIER ,dbo.PAR_CATEGORIE ,dbo.PAR_PIDENT, PAR_TITRE
WHERE dbo .CSR_DOSSIER.DOS_ID =(SELECT dbo.CSR_DOSSIER.DOS_ID
FROM dbo.CSR_DOSSIER WHERE DOS_NODE =@node )
AND CSR_DOSSIER.CAT_CODE = PAR_CATEGORIE .CAT_CODE
AND CSR_DOSSIER.PID_CODE = PAR_PIDENT.PID_CODE
AND CSR_DOSSIER.TIT_CODE = PAR_TITRE.TIT_CODE;
set @resultat ='Paiement Partiel';
end
return @resultat
end try
begin catch
SELECT ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;--need this rollback with the commit added below
end catch
IF @@TRANCOUNT > 0
COMMIT--need this missing commit
END
Jared
CE - Microsoft
August 10, 2012 at 1:36 am
Merci, it's works!
thanks
PS: how to sate that a post is closed or solved?
August 10, 2012 at 3:09 am
thanks a lot.
is it possible for procedure to have 02 output parameters?
how can i do it? and use them
August 10, 2012 at 5:58 am
marclas (8/10/2012)
thanks a lot.is it possible for procedure to have 02 output parameters?
how can i do it? and use them
You do it the same way you output 1, you just add another to the list.
http://msdn.microsoft.com/en-us/library/ms187004%28v=sql.105%29.aspx
Also, you don't have to resolve/close a post here. They stay open for others to comment or add to.
Jared
CE - Microsoft
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy