Fonction avec parametre table

[Renaud°]

Bonjour,

J'essaye de réaliser une fonction qui reçoit un paramètre table, et j'ai un message surprenant

voici un exemple :

Tout d'abord, créer un type Table et la fonction qui va recevoir ce 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
CREATE Type tbl_ListeProrata AS TABLE
(
	Id		varchar(255)	NOT NULL,
	Montant	money		NULL
)
go
 
CREATE FUNCTION dbo.fn_Prorata (@Liste AS dbo.tbl_ListeProrata ReadOnly)
RETURNS @Result TABLE
(
	Id		varchar(255)	NOT NULL,
	Montant	money		NULL,
	Coeff	decimal(20,17)		NULL
)
AS BEGIN
	WITH Toto(Total) AS 
	( SELECT Total = Sum(IsNull(Montant,0)) FROM @Liste)
 
	INSERT @Result
	SELECT L.Id, L.Montant, L.Montant/T.Total  
	FROM	@Liste AS L, Toto AS T
 
	RETURN
END
go

Puis le test

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
CREATE TABLE #Tata
(	IdTata	int NOT NULL,
	Montant	money NULL
)
 
INSERT INTO #Tata Values( 1, 158.10)
INSERT INTO #Tata Values( 2, 780.5)
INSERT INTO #Tata Values( 3, 980.25)
INSERT INTO #Tata Values( 4, 1980.25)
INSERT INTO #Tata Values( 5, 1245)
 
Declare @AffListe AS dbo.tbl_ListeProrata
 
INSERT INTO @AffListe (Id,Montant)
SELECT Id = convert(varchar(255),IdTata), Montant = Montant FROM #Tata
 
SELECT *
FROM dbo.fn_Prorata(@AffListe)

Et Sql Server me répond :

La variable scalaire "@AffListe" doit être déclarée.

J'ai beau chercher, je ne vois pas où je faute ...

Et sur le net, outre Microsoft qui décrit les étapes pour y parvenir complété d'un exemple ... de procédure stocké, je n'ai trouvé que ça voir la réponse de Lukasz Lysik du Oct 22 '09 at 18:42

Son exemple tel quel fonctionne, dès que je le transforme pour que la fonction retourne une table, le message revient !

Est-ce à dire qu'une fonction qui reçoit une table ne peut pas en renvoyer elle même une ?

[elsuket]

Bonjour,

Je viens de faire le test sur mon instance SQL Server 2008 R2, et en fait votre erreur n'est pas dans le code mais dans la façon dont vous l'exécutez manuellement.

Vous avez dû exécuter :

Code :

1
2
3
4
5
6
7
8
9
10
11
 
CREATE TABLE #Tata
(	IdTata	int NOT NULL,
	Montant	money NULL
)
 
INSERT INTO #Tata Values( 1, 158.10)
INSERT INTO #Tata Values( 2, 780.5)
INSERT INTO #Tata Values( 3, 980.25)
INSERT INTO #Tata Values( 4, 1980.25)
INSERT INTO #Tata Values( 5, 1245)

Puis :

Code :

Declare @AffListe AS dbo.tbl_ListeProrata

comme un instruction seule, en la surlignant et en l'exécutant. Puis :

Code :

1
2
3
4
5
INSERT INTO @AffListe (Id,Montant)
SELECT Id = convert(varchar(255),IdTata), Montant = Montant FROM #Tata
 
SELECT *
FROM dbo.fn_Prorata(@AffListe)

Ce qui fait que la variable @AffListe n'existe plus : elle n'a d'existence que pour la durée du lot de requêtes que vous soumettez.

En outre je ne vous conseille pas d'utiliser le type de données money, qui produit des erreurs de calculs bien connues.
Utilisez le type decimal.

@++

[Renaud°]

Bonjour elsuket, et merci de cette réponse

Envoyé par elsuket

Bonjour,

Je viens de faire le test sur mon instance SQL Server 2008 R2, et en fait votre erreur n'est pas dans le code mais dans la façon dont vous l'exécutez manuellement.

Ce qui fait que la variable @AffListe n'existe plus : elle n'a d'existence que pour la durée du lot de requêtes que vous soumettez.

Hélas, non, ce n'est pas le cas, le bloc de code est bien exécuter en une seule fois et non par petit bout qui pourrait mettre @AffListe hors course.

En revanche, si j’exécute le code sans l'appel à la fonction tout se passe correctement, y compris un

Code :

SELECT * FROM @AffListe

Le pb se situe vraiment à l'appel de la fonction, je suis en SQL 2008 (non R2), je vais essayer de trouver de quoi faire un essai sur R2

En outre je ne vous conseille pas d'utiliser le type de données money, qui produit des erreurs de calculs bien connues.
Utilisez le type decimal.
@++

OK, merci du rappel, je pratique SQL depuis pas mal d'années, et récemment me suis demandé pourquoi je n'utilisais pas ce type .... j'ai du oublier

Merci encore, je reviens après avoir essayé sur R2, si j'en trouve un

[iberserk]

Declare @AffListe AS dbo.tbl_ListeProrata

le AS est simplement de trop...

[Renaud°]

e AS est simplement de trop...

non plus, avec ou sans rien de mieux ni de moins bien

Je viens d'essayer sur plusieurs machines, certaines passent d'autre non, et ce n'est pas une question de version puisque sur un site, j'ai deux machines avec versions identiques (2007.100.1600.022) l'une passait, l'autre non.
Plus surprenant, sur ces 2 machines, le serveur ne passait pas et l'autre -machine de dev en seven 64 - passait.

J'avoue que je n'y comprends plus rien.
Une histoire de paramétrage ?

[Renaud°]

J'ai effectué le test sur différentes configurations
constats :

> Fonction recevant une table en paramètre et renvoyant un varchar - RAS
> Fonction recevant un varchar en paramètre et renvoyant une table - RAS
> Fonction recevant une table en paramètre et renvoyant une table
- 2008 Standard 64 (10.0.1600) NOK
- 2008 Standard 64 (10.0.2573) NOK
- 2008 Developper 64 (10.0.1600) OK
- 2008 Standard 64 (10.0.4000) OK
- 2008 Standard 32 (10.0.4064) NOK
- 2008 R2 Express 32 (10.50.1617.0) OK


Etonnant non ?

Si quelqu'un avait une explication ...