【SQL开源代码栏目提醒】:网学会员在SQL开源代码频道为大家收集整理了GetProCount.sql提供大家参考,希望对大家有所帮助!
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION GetProCount (@tid int,@nflag int,@ntype int)
RETURNS int AS
BEGIN
-- 返回指定产品类型销售数量或退货数量
-- 变量@tid表示类型编号;@nflag表示数据类型:0-销售、1-退货;
-- @ntype表示统计类型:0-按产品类别、1-按产品编号
DECLARE @NCount int
IF (@ntype=0)
SELECT @NCount=(SELECT SUM(l.Quantity)
FROM ProductList l,Product p
WHERE l.ProId=p.ProId AND p.TypeId=@tid AND l.Flag=@nflag
GROUP BY p.TypeId)
ELSE
SELECT @NCount=(SELECT SUM(Quantity)
FROM ProductList WHERE ProId=@tid AND Flag=@nflag
GROUP BY ProId)
IF @NCount IS NULL
SET @NCount = 0
RETURN @NCount
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO