Variables complexes dans une procédure stockée

Version imprimable

Bonjour,
Voilà une ligne de code

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
ALTER PROCEDURE [dbo].[ComparaisonDePeriode](@NombrePeriodeCompare as int,@PeriodeCompare as nvarchar(25),@PeriodeFin as int,@Annee as int,@ChaineFiltre as nvarchar(255),@Titre as nvarchar(50))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	if @Periodefin-@NombrePeriodeCompare<0
	begin
	SELECT nom+' ('+CodeArticle+')' as Elt,datename(MONTH,dateoperation)+' '+cast(case when MONTH(dateoperation)>@PeriodeFin then @Annee-1 else @Annee end as nvarchar) as Periode,sum(QteGros+QteDetail/Rapport2) as V,@ChaineFiltre as ChaineFiltre,@Titre as Titre,case when MONTH(dateoperation)>@PeriodeFin then MONTH(dateoperation)-12 else MONTH(dateoperation) end as NbP from EntreeSortie inner join EntreeSortieArticle on EntreeSortie.NumBon=EntreeSortieArticle.NumBon and EntreeSortie.ClientFournisseur=EntreeSortieArticle.ClientFournisseur inner join p_article on EntreeSortieArticle.CodeArticle=p_article.Code where (EntreeSortie.ClientFournisseur='client') and ((month(dateoperation) between  @Periodefin-@NombrePeriodeCompare+13 and 12) and (year(dateoperation)=@annee-1)) or ((month(dateoperation) between 1 and @Periodefin) and (year(dateoperation)=@annee)) group by nom+' ('+CodeArticle+')',datename(MONTH,dateoperation)+' '+cast(case when MONTH(dateoperation)>@PeriodeFin then @Annee-1 else @Annee end as nvarchar),case when MONTH(dateoperation)>@PeriodeFin then MONTH(dateoperation)-12 else MONTH(dateoperation) end
	end
	else
	begin
	SELECT nom+' ('+CodeArticle+')' as Elt,datename(MONTH,dateoperation)+' '+cast(@Annee as nvarchar) as Periode,sum(QteGros+QteDetail/Rapport2) as V,@ChaineFiltre as ChaineFiltre,@Titre as Titre,MONTH(dateoperation) as NbP from EntreeSortie inner join EntreeSortieArticle on EntreeSortie.NumBon=EntreeSortieArticle.NumBon and EntreeSortie.ClientFournisseur=EntreeSortieArticle.ClientFournisseur inner join p_article on EntreeSortieArticle.CodeArticle=p_article.Code where (EntreeSortie.ClientFournisseur='client') and (month(dateoperation) between  @Periodefin-@NombrePeriodeCompare and  @Periodefin) and (year(dateoperation)=@annee) group by nom+' ('+CodeArticle+')',datename(MONTH,dateoperation)+' '+cast(@Annee as nvarchar),MONTH(dateoperation)
	end
END

Est ce possible de créer deux variables

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
@bStr1 as nvarchar(225), @bStr2 as nvarchar(225)
@bstr1=SELECT nom+' ('+CodeArticle+')' as Elt,datename(MONTH,dateoperation)+' '+cast(case when MONTH(dateoperation)>@PeriodeFin then @Annee-1 else @Annee end as nvarchar) as Periode,sum(QteGros+QteDetail/Rapport2) as V,@ChaineFiltre as ChaineFiltre,@Titre as Titre,case when MONTH(dateoperation)>@PeriodeFin then MONTH(dateoperation)-12 else MONTH(dateoperation) end as NbP from EntreeSortie inner join EntreeSortieArticle on EntreeSortie.NumBon=EntreeSortieArticle.NumBon and EntreeSortie.ClientFournisseur=EntreeSortieArticle.ClientFournisseur inner join p_article on EntreeSortieArticle.CodeArticle=p_article.Code where (EntreeSortie.ClientFournisseur='client') and ((month(dateoperation) between  @Periodefin-@NombrePeriodeCompare+13 and 12) and (year(dateoperation)=@annee-1)) or ((month(dateoperation) between 1 and @Periodefin) and (year(dateoperation)=@annee)) group by nom+' ('+CodeArticle+')',datename(MONTH,dateoperation)+' '+cast(case when MONTH(dateoperation)>@PeriodeFin then @Annee-1 else @Annee end as nvarchar),case when MONTH(dateoperation)>@PeriodeFin then MONTH(dateoperation)-12 else MONTH(dateoperation) end
 
@bStr2=SELECT nom+' ('+CodeArticle+')' as Elt,datename(MONTH,dateoperation)+' '+cast(@Annee as nvarchar) as Periode,sum(QteGros+QteDetail/Rapport2) as V,@ChaineFiltre as ChaineFiltre,@Titre as Titre,MONTH(dateoperation) as NbP from EntreeSortie inner join EntreeSortieArticle on EntreeSortie.NumBon=EntreeSortieArticle.NumBon and EntreeSortie.ClientFournisseur=EntreeSortieArticle.ClientFournisseur inner join p_article on EntreeSortieArticle.CodeArticle=p_article.Code where (EntreeSortie.ClientFournisseur='client') and (month(dateoperation) between  @Periodefin-@NombrePeriodeCompare and  @Periodefin) and (year(dateoperation)=@annee) group by nom+' ('+CodeArticle+')',datename(MONTH,dateoperation)+' '+cast(@Annee as nvarchar),MONTH(dateoperation)
 
if @Periodefin-@NombrePeriodeCompare<0
	begin
	@bStr1
	end
	else
	begin
	@bStr2
	end

Je m'excuse des textes longs. Je vous prie de me montrer comment revenir à la ligne dans les balises de code

15/09/2015, 19h59
Invité

Comme ça, non.
Mais avec du TSql dynamique, c'est possible. ( un lien au hasard : https://www.mssqltips.com/sqlservert...in-sql-server/ )
Est-ce que ça serait mieux ? À mon avis, non, car c'est plus difficile de maintenir du code entre Quote.
16/09/2015, 17h04
aieeeuuuuu

Bonjour,

Pour les retours à la ligne dans les balises code, c'est la touche entrée...

Merci 7gyY9w1ZY6ySRgPe.
J'ai suivi les instructions et voilà où je suis arrivé

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ALTER PROCEDURE [dbo].[MouvementDeCaisse] (@DateDebut as nvarchar(10),@DateFin as nvarchar(10),@Chaine as nvarchar(1000),@ChaineFiltre as 
nvarchar(100))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	Declare @SqlCommand as nvarchar(1000)
 
	set @SqlCommand='select dateoperation,motif,sum(entree) as E,sum(sortie) as S,heureoperation,@ChaineFiltre as ChaineFiltre from mvt_caisse where 
(dateoperation between @DateDebut and @DateFin)'+@Chaine+' group by dateoperation,motif,heureoperation order by dateoperation,heureoperation'
	EXEC (@sqlCommand)
END

Quand j'exécute, voilà le code de l'exécution de la procédure stockée

Code:

1
2
3
4
5
6
7
8
9
10
11
DECLARE	@return_value int
 
EXEC	@return_value = [dbo].[MouvementDeCaisse]
		@DateDebut = N'01/01/2015',
		@DateFin = N'31/01/2015',
		@Chaine = N'',
		@ChaineFiltre = N'XX'
 
SELECT	'Return Value' = @return_value
 
GO

et le code d'erreur suivant

Code:

1
2
3
4
Msg*137, Niveau*15, État*2, Ligne*1
La variable scalaire "@ChaineFiltre" doit être déclarée.
 
(1*ligne(s) affectée(s))

Je voudrais comprendre ce qui n'a pas marché

17/09/2015, 14h22
aieeeuuuuu

EXEC exécute la commande dans un nouveau contexte. Dans ce contexte, @chaineFiltre n'existe pas d'où votre message.

Vous pouvez soit
- concaténer cette variable comme vous l'avez fait pour @chaine (mais attention, cette pratique est dangereuse car sensible aux attaques par injection
SQL. Contrôlez bien le contenu de cette variable)
- passer explicitement votre paramètre en utilisant sp_executesql

Je voudrais cette syntaxe avant de passer à sp_executesql.
Voilà donc le code réamenagé.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
ALTER PROCEDURE [dbo].[MouvementDeCaisse] (@DateDebut as nvarchar(10),@DateFin as nvarchar(10),@Chaine as nvarchar(1000),@ChaineFiltre as nvarchar(100))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	Declare @SqlCommand as nvarchar(1000)
 
	set @SqlCommand='select dateoperation,motif,sum(entree) as E,sum(sortie) as S,heureoperation,'+@ChaineFiltre+' as ChaineFiltre from mvt_caisse
 where (dateoperation between '+@DateDebut+' and '+@DateFin+')'+@Chaine+' group by dateoperation,motif,heureoperation order by dateoperation,heureoperation'
	EXEC (@sqlCommand)
END

Voilà le message d'erreur qu'il m'affiche

Code:

1
2
3
4
5
6
Msg*206, Niveau*16, État*2, Ligne*1
Conflit de types d'opérandes*: date est incompatible avec int
Msg*207, Niveau*16, État*1, Ligne*1
Nom de colonne non valide*: 'XX'.
 
(1*ligne(s) affectée(s))

Je n'ai pas de valeur de type date et int. comment les deux se retrouvent dans une incompatibilité.
par ailleurs, 'XX' est la valeur de la colonne 'ChaineFiltre'

17/09/2015, 15h03
aieeeuuuuu

votre colonne dateoperation est certainement de type date.

Vous effectuez une BEWEEN dessus, mais vous n'avez pas mis de guillemets autour des dates. Elles sont donc mal interprétées.

un conseil : faites un PRINT de votre commande avant de l’exécuter, vous verrez ce qu'elle contient réellement.

Comment on utilise le print.
j'ai fait

Code:

1
2
3
4
set @SqlCommand='select dateoperation,motif,sum(entree) as E,sum(sortie) as S,heureoperation,'+@ChaineFiltre+' as ChaineFiltre from mvt_caisse
 where (dateoperation between '+@DateDebut+' and '+@DateFin+')'+@Chaine+' group by dateoperation,motif,heureoperation order by dateoperation,heureoperation'
	PRINT @sqlcommand
	EXEC (@sqlCommand)

mais à l'exécution, je ne vois rien

17/09/2015, 17h03
aieeeuuuuu

L'utilisation est correcte.

L'affichage n'est cependant pas forcément immédiat, il faut donc attendre la fin de l’exécution du batch.

Vous pouvez aussi faire du pas à pas et vérifier le contenu de votre variable.

Vous êtes formidables.
Je sais que je peux compter sur vous.

Voilà le code final

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
ALTER PROCEDURE [dbo].[MouvementDeCaisse] (@DateDebut as nvarchar(50),@DateFin as nvarchar(10),@Chaine as nvarchar(1000),@ChaineFiltre as nvarchar(100))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	Declare @SqlCommand as nvarchar(1000)
 
	set @SqlCommand='select dateoperation,motif,(select sum(entree-sortie) from mvt_caisse where (dateoperation<'''+@DateDebut+''')) as Prec,
sum(entree) as E,sum(sortie) as S,heureoperation,'''+@ChaineFiltre+''' as ChaineFiltre,''PERIODE DU '+@DateDebut+' AU '+@DateFin+''' as Periode from
mvt_caisse where (dateoperation between '''+@DateDebut+''' and '''+@DateFin+''')'+@Chaine+' group by dateoperation,motif,heureoperation order by
dateoperation,heureoperation'
	EXEC (@sqlCommand)
END

et voilà le code d'exécution

Code:

1
2
3
4
5
6
7
8
9
10
11
DECLARE	@return_value int
 
EXEC	@return_value = [dbo].[MouvementDeCaisse]
		@DateDebut = N'01/01/2015',
		@DateFin = N'31/01/2015',
		@Chaine = N' and (mp=''espèce'')',
		@ChaineFiltre = N'XX'
 
SELECT	'Return Value' = @return_value
 
GO

Mille mercis
Maintenant, pour éviter les injections sql, je vais essayer d'utiliser sp_executesql

1 pièce(s) jointe(s)

Ce n'est peut être pas l'endroit pour poser le problème. Je vous prie de ce fait de me rediriger vers le bon forum.
En effet, ma procédure stockée marche à merveille.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
ALTER PROCEDURE [dbo].[MouvementDeCaisse] (@DateDebut as nvarchar(50),@DateFin as nvarchar(10),@Chaine as nvarchar(1000),@ChaineFiltre as nvarchar(100))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	Declare @SqlCommand as nvarchar(1000)
 
		set @SqlCommand='select cast(dateoperation as nvarchar)+'' ''+cast(heureoperation as nvarchar) as DO,motif,(select sum(entree-sortie) from
mvt_caisse where (dateoperation<'''+@DateDebut+''')) as Prec, sum(entree) as Recette,sum(sortie) as Depense,'''+@ChaineFiltre+''' as ChaineFiltre,''PERIODE
DU '+@DateDebut+' AU '+@DateFin+''' as Periode from mvt_caisse where (dateoperation between '''+@DateDebut+''' and '''+@DateFin+''')'+@Chaine+' group by
dateoperation,motif,heureoperation order by dateoperation,heureoperation'
 
	EXECUTE sp_executesql @sqlCommand
	PRINT @sqlCommand
END

Mais dans dans mon code VB 2012, lorsque je veux ajouter une source de donnée, cette procédure ne m'affiche aucun champ que je peux sélectionner selon l'image ci-dessous.
Pièce jointe 188245

A quoi celà est dû? Qu'est ce que je dois faire?

18/09/2015, 10h18
aieeeuuuuu

En général, les outils qui explorent le retour des procédures stockées (ORM, ...) appellent celles-ci après avoir activé l'option FMTONLY, afin que l’exécution de la procédure ne produise aucun effet (cela permet donc juste de savoir ce que la procédure va retourner, sans exécuter réellement sont contenu).

Dans votre cas, le EXEC n'est donc pas réellement exécuté, et votre procédure semble ne rien renvoyer. Vous pouvez certainement renseigner manuellement le retour de la SP.

Une astuce qui peut fonctionner, étant donné que votre SP ne fait que du SELECT : placez un SET FMTONLY OFF en début de procédure stockée afin qu'elle soit réellement exécutée.
18/09/2015, 10h41
djelloharmel

j'ai placé l'expression SET FMTONLY OFF à chaque fois à une ligne de la sp. ça ne donne rien de l'autre coté en vb.
je peux pas avoir un exemple concret?
18/09/2015, 10h50
aieeeuuuuu

Ce n'était qu'une supposition.

L'outil que vous utilisez se sert peut-être de PARSEONLY à la place de FMTONLY.

par ailleurs, les paramètre fournis automatiquement pas l'outil (probablement NULL) provoquent peut-être aussi une erreur de la procédure stockée.

profilez l'appel de la SP par l'outil pour comprendre ce qui se passe et trouver une parade, sinon renseignez à la main de la résultat attendu de la SP.
19/09/2015, 07h39
djelloharmel

Je continue de fouiller et j'essaie tout ce qui me tombe sous la main mais en vain.
Peut être que mon problème est mal posé.
Dans l'image que j'ai joint, on voit clairement que la sp BilanFinacier se déroule et affiche les champs. Par contre, MouvementDeCaisse n'en fait pas autant.
En passant par des variables dans un sp, est-ce que on peut retrouver les champs comme c'est le cas pour pour BilanFinacier en faisant une liaison de données du coté de vb.
Si l'expression OUTPUT ou OUT peut résoudre le problème, comment je peux les utiliser dans mon cas
21/09/2015, 09h50
aieeeuuuuu

il est aussi probable que l'outil appel la procédure avec des NULL pour tous les paramètres.

Du coup, votre chaine contenant normalement la requête SQL est NULL également.

renseignez en dur les variables d'entrée dans votre procédure pour le temps d'importer votre SP, puis enlevez-les.
25/09/2015, 12h11
djelloharmel

Merci beaucoup du coup de main
ça marche