Discussion :

[luc_chivas]

Bonjour,

voilà.. j'ai fais une fonction sous SQL Server 2005 qui ressemble ça..

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
19
20
21
22
23
CREATE Function  [dbo].[ArrayFacturedu] (@Num int)
Returns Table
as
Return
 
  SELECT FACTURE_ORIGINE.FT_NUM ORI_FTNUM,
		FACTURE.FT_NUM, 
		FACTURE.FT_DATE, 
		FACTURE.FA_MAT, 
		FACTURE.FT_MONT, 
		FACTURE.TE_NUM, 
		TERRAIN.TE_NOM, 
		FACTURE.FT_RESTE, 
		FACTURE.FT_ORDRE, 
		FACTURE.FT_TOTAL
 FROM   FACTURE FACTURE ,
		TERRAIN TERRAIN,
		FACTURE FACTURE_ORIGINE
 WHERE  FACTURE.FT_RESTE>0 AND
		TERRAIN.TE_NUM = FACTURE.TE_NUM AND 
		FACTURE.TE_NUM=FACTURE_ORIGINE.TE_NUM AND
		FACTURE.FA_MAT=FACTURE_ORIGINE.FA_MAT AND
		FACTURE_ORIGINE.FT_NUM = @NUM

J'ai insérée cette fonction dans une 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
12
13
Select structure.st_num,st_nom, st_ville, st_naf, st_siret, st_com, st_logo,
      te_nom, te_ad1, te_cp, te_ville, te_com,te_tel, te_fax,
      fa_nom, fa_prenom, se_entree,em_code, dbo.impaye(facture.te_num, facture.fa_mat) as Impaye,
      facture.*,
      facture_ori.ft_num as ft_num_origin,
      facture_ori.te_num as te_num_origin,
      ligne.*,
      civil.ci_lib,
      civil1.ci_lib as ci_lib_co,
      fa_conom,
      fa_coprenom,dbo.caution (facture.se_num) as caution,
		dbo.acompte(facture.te_num, facture.fa_mat) as Acompte,
	 dbo.ArrayFacturedu (facture_ori.ft_num) as impaye

Comme vous pouvez le voir, j'ai d'autres fonction dans cette SP...
ALTER et create fonctionne très bien sur la fonction et la SP... par contre en utilisation j'ai le message d'erreur

"impossible de trouver la colonne dbo, la fonction définie par l'utilisateur ou l'agrégat....."...

une idée ???

D'avance merci

[SQLpro]

C'est plus lisible en mettant les balise code (symbole # en haut de la fenêtre de saisie).

Cette fonction vous renvoyant une table, elle ne peut figurer en tant que colonne !

A +

[elsuket]

Bonjour,

Pour compléter la réponse de SQLPro, je ne suis pas certain que vous ayiez besoin d'une fonction pour faire ce dont vous avez besoin.
Cela induit le calcul de votre requête pour chaque ligne qui serait retournée par la requête de la procédure stockée, un peu à la façon d'un curseur.

D'autre part vous devriez séparer, dans votre fonction, les prédicats de jointure des filtres de votre requête :

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
19
20
21
22
23
CREATE FUNCTION  [dbo].[ArrayFacturedu]
	(@Num INT)
RETURNS TABLE
AS
RETURN
	SELECT FACT_ORIG.FT_NUM AS ORI_FTNUM,
			FACT.FT_NUM,
			FACT.FT_DATE,
			FACT.FA_MAT,
			FACT.FT_MONT,
			FACT.TE_NUM,
			TER.TE_NOM,
			FACT.FT_RESTE,
			FACT.FT_ORDRE,
			FACT.FT_TOTAL
	FROM dbo.FACTURE AS FACT
	JOIN dbo.TERRAIN AS TER
		ON TER.TE_NUM = FACT.TE_NUM
	JOIN dbo.FACTURE AS FACT_ORIG
		ON FACT.TE_NUM = FACT_ORIG.TE_NUM
		AND FACT.FA_MAT = FACT_ORIG.FA_MAT
	WHERE FACT.FT_RESTE > 0
	AND FACTURE_ORIGINE.FT_NUM = @NUM

En effet, si le compilateur de SQL Server est suffisamment intelligent pour réaliser les jointures lui-même, il est existe des cas où ce type de jointure est contre-performant.
En faisant cela vous respecterez de plus la norme et améliorerez la lisibilité de votre code, tout en facilitant le travail du compilateur

Pour voir le résultat d'une fonction table dans une requête, vous devez utiliser l'opérateur APPLY (introduit avec SQL Server 2005), mais à ce moment là vous aurez le produit cartésien du résultat de la requête de votre procédure stockée par le résultat de votre fonction, et je doute que ce soit ce que vous cherchez.

Voici un exemple d'utilisation de APPLY :

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
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- Création de la fonction table
CREATE FUNCTION Fn_Multiples (@i INT)
	RETURNS TABLE
	WITH SCHEMABINDING
AS
RETURN
(
		SELECT 2 * @i AS Multiple
	UNION
		SELECT 3 * @i AS Multiple
	UNION
		SELECT 7 * @i AS Multiple
)
GO
 
-- Une petite table de nombres
CREATE TABLE Nombres
(
	unNombre INT NOT NULL
)
GO
 
-- Un petit jeu de données
INSERT INTO dbo.Nombres VALUES (1)
INSERT INTO dbo.Nombres VALUES (2)
INSERT INTO dbo.Nombres VALUES (3)
 
-- Exemple de requête
SET NOCOUNT ON
 
SELECT N.unNombre,
		FN_M.Multiple
FROM dbo.Nombres AS N
CROSS APPLY dbo.Fn_Multiples(unNombre) AS FN_M

On aurait pu aussi écrire :

Code :

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

1
2
3
4
5
6
7
8
9
10
-- Création de la fonction table
ALTER FUNCTION Fn_Multiples (@i INT)
	RETURNS TABLE
	WITH SCHEMABINDING
AS
RETURN
(
		SELECT 2 * @i AS Multiple2, 3 * @i AS Multiple3, 7 * @i AS Multiple7
)
GO

Et le résultat n'est pas le même

Vous pouvez aussi utiliser l'opérateur OUTER APPLY, qui retourne, à l'image d'une jointure externe, la valeur NULL lorsqu'aucune valeur ne correspond aux critères définis dans la fonction table :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
-- Création de la fonction table
ALTER FUNCTION Fn_Multiples (@i INT)
	RETURNS TABLE
	WITH SCHEMABINDING
AS
RETURN
(
		SELECT 2 * @i AS Multiple2, 3 * @i AS Multiple3, 7 * @i AS Multiple7
		WHERE @i > 2
)
GO

La requête suivante :

Code :

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

1
2
3
4
5
6
SELECT N.unNombre,
		FN_M.Multiple2,
		FN_M.Multiple3,
		FN_M.Multiple7
FROM dbo.Nombres AS N
OUTER APPLY dbo.Fn_Multiples(unNombre) AS FN_M

Retourne NULL pour les colonnes Multiple2, Multiple3 et Multiple7 de la fonction, puisque la colonne unNombre de la table Nombres contient les valeurs 1 et 2

Peut être que vous pouvez écrire :

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
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SELECT structure.st_num,
		st_nom,
		st_ville,
		st_naf,
		st_siret,
		st_com,
		st_logo,
		te_nom,
		te_ad1,
		te_cp,
		te_ville,
		te_com,
		te_tel,
		te_fax,
		fa_nom,
		fa_prenom,
		se_entree,
		em_code,
		dbo.impaye(facture.te_num, facture.fa_mat) AS Impaye,
		facture.*,
		facture_ori.ft_num AS ft_num_origin,
		facture_ori.te_num AS te_num_origin,
		ligne.*,
		civil.ci_lib,
		civil1.ci_lib AS ci_lib_co,
		fa_conom,
		fa_coprenom,
		dbo.caution (facture.se_num) AS caution,
		dbo.acompte(facture.te_num, facture.fa_mat) AS Acompte,
		AF.*
		OUTER APPLY dbo.ArrayFacturedu (facture_ori.ft_num) AS AF
FROM ...

Néanmoins je crois qu'en réalisant directement la jointure sur la colonne facture_ori.ft_num vous ferait gagner du temps et des performances.

@++

[luc_chivas]

C'est plus lisible en mettant les balise code (symbole # en haut de la fenêtre de saisie).

Cette fonction vous renvoyant une table, elle ne peut figurer en tant que colonne !

A +

Merci à tous pour vos réponses plus que complètes.
J'aurais souhaité que cette requête retourne un donnée de type array, que je saurais traiter ensuite... et non pas une table.. mais c'est un exemple que j'avais trouvé.

[elsuket]

Dites-nous ce que vous voulez faire exactement, nous pourrons probablement vous aider

@++

[luc_chivas]

Dites-nous ce que vous voulez faire exactement, nous pourrons probablement vous aider

@++

Merci d'avance de votre attention.

alors,

Cette procédure stockée (sans cette fonction) est utlisée dans un rapport Crystal.
En principe, il y a un sous rapport qui s'appuie sur la fonction que j'essaye de crééer ( actuellement une autre SP). l'application qui gère le rapport Crystal gère les sous rapport, mais pas de lamnière qui m'intéresse.
Je cherche donc à remonter un deuxième dataset (sous forme d'array) dans une seule Procédure stockée afin de ne pas faire appel à un sous rapport. Cette laborieuse fonction a un paramètre qui vient de la procédure principale. @num.

Cette manière de faire risque de revenir souvent pour d'autres rapports.

J'espère que j'ai été plus clair.

[elsuket]

Qu'est-ce qui vous empêche dans ce cas de réaliser directement la jointure ?

@++

[luc_chivas]

Qu'est-ce qui vous empêche dans ce cas de réaliser directement la jointure ?

@++

heu... juste la connnaissance...... mai slà, je vois pas trop le type de jointure.....

[luc_chivas]

heu... juste la connnaissance...... mai slà, je vois pas trop le type de jointure.....

en fait, je voudrait récupérer dans un champs unique de ma procédure, le résultat d'une requête (eventuellement avec des séparateurs).

volà, voilà...