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
| create function CA2_Fournisseur (
@codefournisseur int,
@annee smalldatetime
)
returns @toto table (numFou int null , nomfou varchar(25)null,CA real null)
as
begin
declare
@tempo table (numFou int , nomfou varchar(25),CA real)
if (not exists( select numFou from fournis where numFou =@codefournisseur))
begin
RAISERROR(50005,1,12,@codefournisseur)
end
else
insert into @tempo
SELECT FOURNIS.NUMFOU,FOURNIS.NOMFOU,sum((LIGCOM.QTECDE*LIGCOM.PRIUNI)*1.2) as 'CA Potentiel'
FROM FOURNIS,LIGCOM,ENTCOM
WHERE
entcom.numcom = ligcom.numcom and
FOURNIS.NUMFOU = entcom.numfou
and
FOURNIS.NUMFOU=@codefournisseur
and
year( Datcom) = @annee
GROUP BY FOURNIS.NUMFOU, FOURNIS.NOMFOU
return
end |
Partager