Requete récursive dans procédure stockée

- - Dernière réponse :  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
Afficher la suite 

2 réponses

Messages postés
3485
Date d'inscription
jeudi 16 juin 2005
Statut
Membre
Dernière intervention
14 octobre 2019
915
0
Merci
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
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 !
Commenter la réponse de Reivax962
0
Merci
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
Reivax962
Messages postés
3485
Date d'inscription
jeudi 16 juin 2005
Statut
Membre
Dernière intervention
14 octobre 2019
915 -
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
> Reivax962
Messages postés
3485
Date d'inscription
jeudi 16 juin 2005
Statut
Membre
Dernière intervention
14 octobre 2019
-
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;
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
Reivax962
Messages postés
3485
Date d'inscription
jeudi 16 juin 2005
Statut
Membre
Dernière intervention
14 octobre 2019
915 -
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
Bonjour XAvier,

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