＋设为首页 ＋添加到收藏夹
CREATE PROCEDURE hztj
@dt1 datetime,@dt2 datetime,@dt3 datetime
AS
declare @d_order_pbsl numeric(10,2),@d_order_dhsl numeric(10,2),@m_order_pbsl numeric(10,2),@m_order_dhsl numeric(10,2)
set @d_order_pbsl=( select sum(order_mx.pbsl) from order_main,order_mx where order_mx.no = order_main.id and order_main.kdrq >=@dt1 and order_main.kdrq < @dt2 )
set @d_order_dhsl=(select sum(order_mx.yhsl) from order_main,order_mx where order_mx.no = order_main.id and order_main.kdrq>=@dt1 and order_main.kdrq <@dt2)
set @m_order_pbsl=(select sum(order_mx.pbsl) from order_main,order_mx where order_mx.no = order_main.id and order_main.kdrq>=@dt2 and order_main.kdrq <=@dt3)
set @m_order_dhsl=(select sum(order_mx.yhsl) from order_main,order_mx where order_mx.no = order_main.id and order_main.kdrq>=@dt2 and order_main.kdrq <=@dt3)
GO
1、将上述存储过程中的变量显示到数据窗口中，要怎样实现？
2、我在做数据窗口时增加了四个相同的变量，这样能显示出来吗？
3、存储过程用参数，在数据窗口检索时，是否可以用下面格式，dw_1.retrieve(d1,d2,d3)?
在數据窗口中使用存儲過程做數据源時,并不是所有存儲過程都可以的.它需要存儲過程有一個游標型的傳出參數,不然DW怎么可能得到數据?
SQL Server我沒有用過,所以不知道應該怎么給你實例.
SP要返回结果集(记录集),才能作为DW的数据源.
同意楼上!
CREATE PROCEDURE hztj
@dt1 datetime,@dt2 datetime,@dt3 datetime
AS
declare @d_order_pbsl numeric(10,2),@d_order_dhsl numeric(10,2),@m_order_pbsl numeric(10,2),@m_order_dhsl numeric(10,2)
set @d_order_pbsl=( select sum(order_mx.pbsl) from order_main,order_mx where order_mx.no = order_main.id and order_main.kdrq >=@dt1 and order_main.kdrq < @dt2 )
set @d_order_dhsl=(select sum(order_mx.yhsl) from order_main,order_mx where order_mx.no = order_main.id and order_main.kdrq>=@dt1 and order_main.kdrq <@dt2)
set @m_order_pbsl=(select sum(order_mx.pbsl) from order_main,order_mx where order_mx.no = order_main.id and order_main.kdrq>=@dt2 and order_main.kdrq <=@dt3)
set @m_order_dhsl=(select sum(order_mx.yhsl) from order_main,order_mx where order_mx.no = order_main.id and order_main.kdrq>=@dt2 and order_main.kdrq <=@dt3)
--返回结果集合－－－－－－－－－－－－－－－－－－－－－－－－－
select @d_order_pbsl ,@d_order_dhsl ,@m_order_pbsl ,@m_order_dhsl
--－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
GO
这样写更好,用as 加上列名
select @d_order_pbsl as 列名,@d_order_dhsl as ...,@m_order_pbsl as ...,@m_order_dhsl as ...
