Variables complexes dans une procédure stockée

**djelloharmel** · 15/09/2015, 18h27

Bonjour,
Voilà une ligne de code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Invité · 15/09/2015, 19h59

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.

**aieeeuuuuu** · 16/09/2015, 17h04

Bonjour,

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

**djelloharmel** · 17/09/2015, 11h36

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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é

**aieeeuuuuu** · 17/09/2015, 14h22

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

**djelloharmel** · 17/09/2015, 14h39

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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'

**aieeeuuuuu** · 17/09/2015, 15h03

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.

**djelloharmel** · 17/09/2015, 16h39

Comment on utilise le print.
j'ai fait

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**aieeeuuuuu** · 17/09/2015, 17h03

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.

**djelloharmel** · 17/09/2015, 22h15

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

Voilà le code final

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**djelloharmel** · 18/09/2015, 04h56

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.
Nom : sp.png
Affichages : 496
Taille : 26,4 Ko

Nom : sp.png
Affichages : 496
Taille : 26,4 Ko

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

**aieeeuuuuu** · 18/09/2015, 10h18

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.

**djelloharmel** · 18/09/2015, 10h41

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?

**aieeeuuuuu** · 18/09/2015, 10h50

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.

**djelloharmel** · 19/09/2015, 07h39

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

**aieeeuuuuu** · 21/09/2015, 09h50

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.

**djelloharmel** · 25/09/2015, 12h11

Merci beaucoup du coup de main
ça marche

Variables complexes dans une procédure stockée [2012]

Développement SQL Server

Discussions similaires

Partager

Partager