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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply