CREATE PROCEDURE [dbo].[RPT_产品分组销售明细]
@StartTime datetime,
@CloesTime datetime,
@TID int
AS
SELECT c.产品编号, c.产品名称_1, e.单价级别名称_1,
d.主要级别名称_1, a.单价, SUM(a.数量 * a.份数 / a.份数分割) AS 数量, SUM(a.金额)
AS 金额, SUM(a.折扣金额) AS 折扣金额, SUM(a.成本) AS 成本, f.分组名称_1, SUM(a.服务费金额) AS 服务费FROM n_V_库明细 a
INNER JOIN 产品表 c ON a.产品_ID = c.产品_ID
INNER JOIN 主要级别字典 d ON a.主要级别_ID = d.主要级别_ID
INNER JOIN 产品单价级别字典 e ON a.单价级别_ID = e.单价级别_ID
INNER JOIN n_V_库订单 b ON a.订单_ID = b.订单_ID AND a.日结日期 = b.日结日期
INNER JOIN 产品分组字典 f ON a.销售分组 = f.分组编号WHERE (a.项目类型 IN (0, 3))
AND (f.统计类型 = 1)
AND (a.日结日期 >= @StartTime)
AND (a.日结日期 <= @CloesTime)
and (f.分组编号=@TID)
GROUP BY c.产品编号, c.产品名称_1, e.单价级别名称_1, a.单价, f.分组名称_1,
d.主要级别名称_1
ORDER BY c.产品编号
GO
@StartTime datetime,
@CloesTime datetime,
@TID int
AS
SELECT c.产品编号, c.产品名称_1, e.单价级别名称_1,
d.主要级别名称_1, a.单价, SUM(a.数量 * a.份数 / a.份数分割) AS 数量, SUM(a.金额)
AS 金额, SUM(a.折扣金额) AS 折扣金额, SUM(a.成本) AS 成本, f.分组名称_1, SUM(a.服务费金额) AS 服务费FROM n_V_库明细 a
INNER JOIN 产品表 c ON a.产品_ID = c.产品_ID
INNER JOIN 主要级别字典 d ON a.主要级别_ID = d.主要级别_ID
INNER JOIN 产品单价级别字典 e ON a.单价级别_ID = e.单价级别_ID
INNER JOIN n_V_库订单 b ON a.订单_ID = b.订单_ID AND a.日结日期 = b.日结日期
INNER JOIN 产品分组字典 f ON a.销售分组 = f.分组编号WHERE (a.项目类型 IN (0, 3))
AND (f.统计类型 = 1)
AND (a.日结日期 >= @StartTime)
AND (a.日结日期 <= @CloesTime)
and (f.分组编号=@TID)
GROUP BY c.产品编号, c.产品名称_1, e.单价级别名称_1, a.单价, f.分组名称_1,
d.主要级别名称_1
ORDER BY c.产品编号
GO
明白了,但是加参数的话那就得动态执行CREATE PROCEDURE [dbo].[RPT_产品分组销售明细]
@StartTime datetime,
@CloesTime datetime,
@TID int
AS
declare @str varchar(max)
set @str='
SELECT c.产品编号, c.产品名称_1, e.单价级别名称_1,
d.主要级别名称_1, a.单价, SUM(a.数量 * a.份数 / a.份数分割) AS 数量, SUM(a.金额)
AS 金额, SUM(a.折扣金额) AS 折扣金额, SUM(a.成本) AS 成本, f.分组名称_1, SUM(a.服务费金额) AS 服务费FROM n_V_库明细 a
INNER JOIN 产品表 c ON a.产品_ID = c.产品_ID
INNER JOIN 主要级别字典 d ON a.主要级别_ID = d.主要级别_ID
INNER JOIN 产品单价级别字典 e ON a.单价级别_ID = e.单价级别_ID
INNER JOIN n_V_库订单 b ON a.订单_ID = b.订单_ID AND a.日结日期 = b.日结日期
INNER JOIN 产品分组字典 f ON a.销售分组 = f.分组编号WHERE (a.项目类型 IN (0, 3))
AND (f.统计类型 = 1)
AND (a.日结日期 >='+ @StartTime+') '
AND (a.日结日期 <='+ @CloesTime+') '
and (f.分组编号='+'@TID+')
GROUP BY c.产品编号, c.产品名称_1, e.单价级别名称_1, a.单价, f.分组名称_1,
d.主要级别名称_1
ORDER BY '+'c.'+你添加的变量
exec(@str)
GO
@StartTime datetime,
@CloesTime datetime,
@TID int,
@paixu varchar(20)
AS
select @paixu=case @paixu when '1' then '产品编号' when '2' then '数量'
when '3' then 金额'' else '' end
declare @str varchar(max)
set @str='
SELECT c.产品编号, c.产品名称_1, e.单价级别名称_1,
d.主要级别名称_1, a.单价, SUM(a.数量 * a.份数 / a.份数分割) AS 数量, SUM(a.金额)
AS 金额, SUM(a.折扣金额) AS 折扣金额, SUM(a.成本) AS 成本, f.分组名称_1, SUM(a.服务费金额) AS 服务费FROM n_V_库明细 a
INNER JOIN 产品表 c ON a.产品_ID = c.产品_ID
INNER JOIN 主要级别字典 d ON a.主要级别_ID = d.主要级别_ID
INNER JOIN 产品单价级别字典 e ON a.单价级别_ID = e.单价级别_ID
INNER JOIN n_V_库订单 b ON a.订单_ID = b.订单_ID AND a.日结日期 = b.日结日期
INNER JOIN 产品分组字典 f ON a.销售分组 = f.分组编号WHERE (a.项目类型 IN (0, 3))
AND (f.统计类型 = 1)
AND (a.日结日期 >='+ @StartTime+') '
AND (a.日结日期 <='+ @CloesTime+') '
and (f.分组编号='+'@TID+')
GROUP BY c.产品编号, c.产品名称_1, e.单价级别名称_1, a.单价, f.分组名称_1,
d.主要级别名称_1
ORDER BY c.'+@paixu
exec(@str)
GO