Discussion :

[VITALTH]

Voilà pour simplifier :
J'ai les tables suivantes SOC1$T1(int i, int j)
SOC2$T1(int i, int j) et SOC3$T1(int i, int j)

Je veux créer une fonction table de type

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
35
36
37
38
39
40
41
42
43
44
CREATE FUNCTION [dbo].[MyFunction](@Companies varchar(500))
RETURNS @ret TABLE 
(	
	           i int,
                         j int
)
AS
 BEGIN
 
DECLARE @qry as varchar(max)
DECLARE @Company as varchar(100)
 
DECLARE  c1 CURSOR FOR 
SELECT Fld FROM dbo.ArrayToTable(@Companies, ',')
-- Dans la variable @companies il y a les sociétés que le l'exécuteur de la fonction veut conserver séparé par des virgules.
-- ex : Si l'executeur veut SOC1 et SOC3, @Companie = 'SOC1,SOC3'
-- Dans notre exemple il y a donc 6 cas de figures 'SOC1', 'SOC2', 'SOC3', 'SOC1,SOC2', 'SOC1,SOC3', 'SOC2,SOC3' et 'SOC1,SOC2,SOC3'
-- La fonction ArrayToTable renvoit une table avec chacune des sociétés comme ligne dans le champ Fld
 
 
 OPEN c1 
 
 FETCH NEXT FROM c1 INTO @Company
 
 SET @qry = 'INSERT INTO @ret
                    SELECT i, j FROM ' + @Company + '$T1 '
 
 FETCH NEXT FROM c1 INTO @Company
 WHILE @@FETCH_STATUS = 0
  BEGIN
 
  SET @qry = @qry + '
                    UNION ALL
                    SELECT i, j FROM ' + @Company + '$T1 '
 
  END
 
 CLOSE c1
 DEALLOCATE c1
 
 Execute SP_executesql @Qry
 
 RETURN
 END

Bon visiblement ça c pas possible puisque visiblement les Procédures sont pas lançable dans une fonction.

Je me demandais si quelqu'un avait une idée.

Merci

[SQLpro]

Une UDF doit avoir un code stable et non transactionnel. Elle ne peut donc pas contenir de SQL dynamique ni de transaction.

Si vous voulez faire cela il faut passer par une procédure.

A +

[VITALTH]

Je ne sais pas ce que c'est qu'une UDF : pour moi c'est un ancien parti politique. (Je plaisante).
Non, sinon j'ai trouvé peut être une solution hier : récupèrer l'ensemble de toutes les tables dans une tables temporaires contenant comme colonne, les colonnes que je veux récupérer plus le nom de la company. Puis faire une requête sur cette même table temporaire en filtrant sur la/les compagnies. Le code ressemblerait à ça avec peut être qq erreurs de syntaxe car je ne l'ai pas encore testé :

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
 
 
CREATE FUNCTION [dbo].[MyFunction](@Companies varchar(500))
RETURNS @ret TABLE 
(	
	           i int,
                         j int
)
AS
 BEGIN
 
DECLARE @Tmp as TABLE as 
( i int, j int, Company varchar(100) )
 
INSERT INTO @Tmp
SELECT i, j, 'SOC1' FROM SOC1$T1
UNION ALL
SELECT i, j, 'SOC2' FROM SOC2$T1
UNION ALL
SELECT i, j, 'SOC3' FROM SOC3$T1
 
 
SET @Companies = '''' + replace(@Companies, ',' , ''',''') + ''''
 
INSERT INTO @ret
SELECT i, j FROM @Tmp WHERE Company in (@Companie)
 
RETURN 
END

Faut que je test mais ça doit marcher à quelques erreurs de syntaxe prêt et peut être un ou deux index de la table temporaire pour aller plus vite.

[VITALTH]

Bon mon truc marche : à un détail prêt : le tableau dans la clause IN m'a fait tourné bourrique.
Du coup je l'ai joué un peu bourrin mais ça marche : j'ai créé une autre table temporaire contenant les sociétés et au lieu de faire une clause WHERE pour filtrer les companys, j'ai fait un inner join entre les deux tables temporaires.
Ca donne ceci :

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
 
 
CREATE FUNCTION [dbo].[MyFunction](@Companies varchar(500))
RETURNS @ret TABLE 
(	
	           i int,
                         j int
)
AS
 BEGIN
 
DECLARE @Tmp as TABLE as 
( i int, j int, Company varchar(100) )
 
DECLARE @TmpSociete as TABLE as
(Company varchar(100))
 
INSERT INTO @TmpSociete (Company)
SELECT Fld FROM dbo.ArrayToTable(@Companies, ',')
-- J'ai repris cette fameuse fonction tabulaire ArrayToTable : c'est pas moi qui l'ai créé je l'ai pompé sur Google, elle est facilement retrouvable.
 
INSERT INTO @Tmp
SELECT i, j, 'SOC1' FROM SOC1$T1
UNION ALL
SELECT i, j, 'SOC2' FROM SOC2$T1
UNION ALL
SELECT i, j, 'SOC3' FROM SOC3$T1
 
INSERT INTO @ret
SELECT i, j FROM @Tmp t INNER JOIN @TmpSociete soc
ON t.Company = soc.Company
 
RETURN 
END

[SQLpro]

UDF : User Defined Function, par opposition aux fonctions intégrées comme CAST ou COALESCE.

Ce que vous faites est une horreur !
Pourquoi créer une table temporaires (par l'intermédiaire d'une variables table) et donc redonder des informations que vous avez déjà, et pourrir vos perf... alors qu'il suffit de faire une vue paramétrée par le biais d'une UDF table inline ?

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
CREATE FUNCTION [dbo].[MyFunction](@Companies varchar(500))
RETURNS  TABLE 
AS
 RETURN
(
SELECT i, j 
FROM   (SELECT i, j, 'SOC1' AS Company
        FROM   SOC1$T1
        UNION  ALL
        SELECT i, j, 'SOC2' 
        FROM   SOC2$T1
        UNION  ALL
        SELECT i, j, 'SOC3' 
        FROM   SOC3$T1) AS t 
       INNER JOIN (SELECT Fld AS Company
                   FROM   dbo.ArrayToTable(@Companies, ',')) AS soc
ON t.Company = soc.Company
)

A +

[VITALTH]

Oui vous avez raison