Requete récursive dans procédure stockée
Bob
-
Bob -
Bob -
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
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
A voir également:
- Inbdocs
- Requête sql pix - Forum Python
- Le point d'entrée de procédure est introuvable dans la bibliothèque de liens dynamiques ✓ - Forum Windows
- Procédure de frigo vide ✓ - Forum Consommation & Internet
- Requête bloquée par le pare-feu applicatif claranet webfence ✓ - Forum Réseaux sociaux
- Requete sql pix - Forum PHP
2 réponses
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
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
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
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
bonjour Xavier,
merci pour ton retour, voici mon code :
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;
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 :
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
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
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 !