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 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
| create function cmdliv2(@code nvarchar(30))
RETURNS nvarchar(30)
as
begin
declare @c integer,@d integer
set @c=(select count (CodeMvtCC) from ArticleCommandeClient where CodeMvtCC=@code)
set @d=(select count (distinct al.CodeMvtLV)
from ArticleLivraison al,Livraison l,LivraisonCommande lc
where al.CodeMvtLV=l.CodeMvtLV and l.CodeMvtLV=lc.CodeMvtLc and lc.CodeMvtCC=@code)
if(@c=@d)
begin
declare @tr bit
set @tr=0
declare @codearticle nvarchar(30),@qte1 real
declare curs1 cursor
For select CodeArticle,Quantite from ArticleCommandeClient
where CodeMvtCC=@code
open curs1
fetch curs1 into @codearticle,@qte1 while ((@@fetch_status=0)and(@tr=0))
begin
fetch curs1 into @codearticle,@qte1
declare @codearticlelivraison nvarchar(30),@cnt real,@qte real
set @cnt=0
declare curs2 cursor
for select al.CodeArticle ,al.Quantite from ArticleLivraison al,
Livraison l,LivraisonCommande lc
where al.CodeMvtLV=l.CodeMvtLV and l.CodeMvtLV=lc.CodeMvtLC
and lc.CodeMvtCC=@code and al.CodeArticle=@codearticle
open curs2
fetch curs2 into @codearticlelivraison,@qte
while @@fetch_status=0
begin
fetch curs2 into @codearticlelivraison,@qte
set @cnt=@cnt+@qte
end
close curs2
deallocate curs2
if(@cnt<@qte1)
set @tr=1
else
set @tr=0
end
close curs1
deallocate curs1
end
declare @final nvarchar(30)
if(@tr=1)
set @final=@code
if(@tr=0)
set @final=0
if(@c>@d)
set @final=@code
if(@d=0)
set @final=@code
return @final
end
--print dbo.cmdliv2(1) |
Partager