Sujet Précédent Sujet Suivant

Requete récursive dans procédure stockée

Fermé
Bob - 19 sept. 2019 à 11:14
 Bob - 1 oct. 2019 à 18:18
Bonjour,

Je suis entrain de développer une procédure stockée qui me permet de récupérer le nombre de d'élément dans une requete.
cette requete la voici :
SET @iNbDocs = 'WITH p(o_projectno) AS
(SELECT o_projectno FROM dms_proj WHERE o_projectno = 5578
UNION ALL
SELECT p1.o_projectno FROM dms_proj AS p1, p
WHERE p.o_projectno = p1.o_parentno)
SELECT count(*) FROM dms_doc WHERE o_projectno IN
(SELECT o_projectno FROM p) and CONVERT( VARCHAR(10), o_credatetime, 103 ) = CONVERT( VARCHAR(10), GETDATE()-1, 103)';

EXECUTE (@iNbDocs);

Cette requete SEULE fonctionne très bien, cependant lorsque je souhaite l'exécuter comme indiqué plus haut, via variable et fonction EXECUTE, j'ai cette erreur :
Conversion failed when converting the nvarchar value 'WITH p(o_projectno) AS ...........

Arpès plusieurs tests, c'est le WITH en début de requete qui est embêtant, avez vous une idée pour exécuter cette requete récursive ?

je suis sous SQL Server 2012

merci d'avance

Configuration: Windows / Chrome 76.0.3809.132

2 réponses

Réponse 1 / 2
Reivax962 Messages postés 3671 Date d'inscription jeudi 16 juin 2005 Statut Membre Dernière intervention 11 février 2021 1 011
19 sept. 2019 à 11:25
Bonjour,

J'ai copié collé ton code dans mon SSMS et ça fonctionne très bien (sauf à l'exécution bien sûr car je n'ai pas les tables en question...)
J'ai simplement rajouté DECLARE @iNbDocs nvarchar(max);

Tu n'as pas un caractère invisible bizarre dans ta requête ?

Xavier
0
Bob
23 sept. 2019 à 15:39
Bonjour Xavier,
merci pour ta réponse
en fait le problème venait de la comparaison, car en effet plus tard dans la procédure je fais une comparason (IF @iNBDoc < 0)...
il fallait mettre le 0 entre ' ' en raison de son type.
merci de m'avoir la puce à l'oreille ;)

résolu !
0
Réponse 2 / 2
Bob
Modifié le 23 sept. 2019 à 17:14
j'ai parlé un peu trop tot, en faite ma comparaison fonctionne correctement, mais lorsque j'essaye de faire afficher le resultat de la requete (qui est un chiffre) dans un text, j'ai toute ma requete SQL qui est affichée...
L'idée est d'envoyer un mail dans la proc stockée EXECUTE [msdb].[dbo].[sp_send_dbmail]

et de récupérer le résultat sous forme de tableau
N'<tr><th bgcolor="#D8D8D8">Nom document(s)</th><th bgcolor="#D8D8D8">Description</th><th bgcolor="#D8D8D8">Date d''arrivée</th></tr>' +
CAST ( (WITH p(o_projectno) AS (SELECT o_proje...

avez vous une solution ?

SET @cMail1_BodyReq =
N'Bonjour, <br><br>' +
N'Nombre de documents : <b>'+ @iNbDocs + '</b> <br><br>' +

merci d'avance

désolé d'avancer au compte goutte mais je remarque et comprend mes erreurs qu'au fil de l'eau
0
Reivax962 Messages postés 3671 Date d'inscription jeudi 16 juin 2005 Statut Membre Dernière intervention 11 février 2021 1 011
23 sept. 2019 à 17:48
Bonjour,

Vu ton problème, je pense qu'il nous faut le code complet de ce que tu essaies de faire. Et pense à bien le coller ici dans les balises < code sql> (En utilisant le bouton à côté de B I S)

Xavier
0
Bob > Reivax962 Messages postés 3671 Date d'inscription jeudi 16 juin 2005 Statut Membre Dernière intervention 11 février 2021
27 sept. 2019 à 15:32
bonjour Xavier,

merci pour ton retour, voici mon code : 

DECLARE @iNbDoc INTEGER,
		@cMail1_BodyReq VARCHAR(MAX);


SET @iNbDoc =		'WITH p(o_projectno) AS
							  (SELECT o_projectno FROM dms_proj WHERE o_projectno = 5561 
							   UNION ALL
							   SELECT p1.o_projectno FROM dms_proj AS p1, p
							   WHERE p.o_projectno = p1.o_parentno)
							SELECT count(*) FROM dms_doc WHERE o_projectno IN 
								(SELECT o_projectno FROM p) and CONVERT( VARCHAR(10), o_credatetime, 103 ) = CONVERT( VARCHAR(10), GETDATE()-1, 103)';
					 
EXECUTE (@iNbDoc);

IF (@iNbDoc > '0')
BEGIN

SET @cMail1_BodyReq =
	N'Bonjour, <br><br> nouveaux documents disponibles<br><br>' + 
    N'<table border="1">' +  
    N'<tr><th bgcolor="#D8D8D8">Nom document(s)</th><th bgcolor="#D8D8D8">Description</th><th bgcolor="#D8D8D8">Date d''arrivée</th></tr>' +    
	CAST ( (WITH p(o_projectno) AS (SELECT o_projectno FROM dms_proj WHERE o_projectno = 5561 UNION ALL SELECT p1.o_projectno FROM dms_proj AS p1, p WHERE p.o_projectno = p1.o_parentno) SELECT td = o_itemname ,
	td = CONVERT(varchar, o_itemdesc, 103), '',
	td = CONVERT(varchar, o_credatetime, 103)	
			FROM dms_doc 
			WHERE o_projectno IN  
			(SELECT o_projectno FROM p) and CONVERT( VARCHAR(10), o_credatetime, 103 ) = CONVERT( VARCHAR(10), GETDATE()-1, 103)
						) 
              FOR XML PATH('tr'), TYPE   
    ) AS NVARCHAR(MAX) ) +  
    N'</table><br><br>' ; 


    EXECUTE [msdb].[dbo].[sp_send_dbmail]
	--etc...
		    
END;
0
Bob
Modifié le 27 sept. 2019 à 19:27
je précise que si ma requete est une "simple" requete (non récursive), le code fonctionne très bien.

merci d'avance pour votre aide
0
Reivax962 Messages postés 3671 Date d'inscription jeudi 16 juin 2005 Statut Membre Dernière intervention 11 février 2021 1 011
Modifié le 30 sept. 2019 à 10:53
Bonjour,

Ton utilisation d'EXEC n'est pas la bonne. EXEC permet d'exécuter une requête construite dynamiquement, mais n'affecte pas son résultat à la variable qui contenait le texte de la requête.
Pour donner une valeur à une variable, tu n'as pas besoin d'EXEC, un simple SELECT @variable = ... suffit.
Je te suggère donc le code suivant : 
DECLARE @iNbDoc INTEGER,
        @cMail1_BodyReq VARCHAR(MAX);
DECLARE @projectnos TABLE (o_projectno int);

WITH p(o_projectno) AS
 (SELECT o_projectno FROM dms_proj WHERE o_projectno = 5561 AND  CONVERT( VARCHAR(10), o_credatetime, 103 ) = CONVERT( VARCHAR(10), GETDATE()-1, 103)
 UNION ALL
 SELECT p1.o_projectno FROM dms_proj AS p1, p
 WHERE p.o_projectno = p1.o_parentno AND CONVERT( VARCHAR(10), p1.o_credatetime, 103 ) = CONVERT( VARCHAR(10), GETDATE()-1, 103))
INSERT INTO @projectnos
SELECT o_projectno FROM p;

SELECT @iNbDoc = count(*) FROM dms_doc
INNER JOIN @projectnos p ON p.o_projectno = dms_doc.o_projectno;

IF (@iNbDoc > '0')
BEGIN
 SELECT @cMail1_BodyReq = 
  CAST ( ( SELECT td = o_itemname, td = CONVERT(varchar, o_itemdesc, 103), '', td = CONVERT(varchar, o_credatetime, 103)
   FROM dms_doc
   INNER JOIN @projectnos p ON p.o_projectno = dms_doc.o_projectno;
   FOR XML PATH('tr')) AS NVARCHAR(MAX));

 SET @cMail1_BodyReq =
 N'Bonjour,(remets ton code HTML ici, le forum me l''interprète !!)' +
  + @cMail1_BodyReq + N'table br br' ;


 EXECUTE [msdb].[dbo].[sp_send_dbmail]
 --etc...

END

J'ai également simplifié ton code en utilisant une variable de type TABLE, qui est remplie au début du script et contient les No dont tu as besoin dans la suite. Ça permet de simplifier mais aussi de gagner en performances.

Xavier

PS : N'ayant pas les données ni même les tables, j'ai écrit ce code un peu à l'aveugle, il se peut qu'il faille le bidouiller pour que ça passe
0
Bob
1 oct. 2019 à 18:18
Bonjour XAvier,

merci pour ta réponse détaillée !
je vais tester tout cela et revenir donner des nouvelles
0

Discussions similaires

problème avec graphQL
dancris11 -
hachel58 -

50 réponses
La requête GraphQL n’est pas autorisée
Mansour -
jaccou -

17 réponses
Point d'entrée introuvable
sam -
Riri41 -

9 réponses
point d'entrée de procédure introuvable
lamarche dominique -
Pat28 -

7 réponses
Commande - “grep” récursif
baissaoui -
baissaoui -

0 réponse