Fonction table à partir d'une requête dynamique

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:

---

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

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 +

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:

---

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.

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:

---

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

---

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:

---

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 +

Oui vous avez raison