【SQL开源代码栏目提醒】:本文主要为网学会员提供GetProSum.sql,希望对需要GetProSum.sql网友有所帮助,学习一下!
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION GetProSum (@tid int,@nflag int,@ntype int)
RETURNS decimal AS
BEGIN
-- 返回指定产品类型销售金额或退货金额
-- 变量@tid表示类型编号;@nflag表示数据类型:0-销售、1-退货;
-- @ntype表示统计类型:0-按产品类别、1-按产品编号
DECLARE @dSum decimal
IF (@ntype=0)
SELECT @dSum=(SELECT SUM(l.Quantity*l.Price)
FROM ProductList l,Product p
WHERE l.ProId=p.ProId AND p.TypeId=@tid AND l.Flag=@nflag
GROUP BY p.TypeId)
ELSE
SELECT @dSum=(SELECT SUM(Quantity*Price)
FROM ProductList WHERE ProId=@tid AND Flag=@nflag
GROUP BY ProId)
IF @dSum IS NULL
SET @dSum = 0
RETURN @dSum
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO