34,576
社区成员
发帖
与我相关
我的任务
分享
CREATE PROC p_test
as
SELECT GETDATE() a
go
CREATE VIEW v_test
AS
SELECT * FROM OPENROWSET('SQLOLEDB.1','server=FI6\SQL2008;uid=sa;pwd=123','exec test11.dbo.p_test')
go
SELECT * FROM V_TEST
/*
a
2009-09-01 11:08:30.197
*/
CREATE FUNCTION FN_TEST()
RETURNS TABLE
AS
RETURN(
SELECT * FROM OPENROWSET('SQLOLEDB.1','server=FI6\SQL2008;uid=sa;pwd=123','exec test11.dbo.p_test')
)
go
SELECT * FROM FN_TEST()
/*
a
2009-09-01 11:08:30.197
*/
7楼厉害,这样函数里面都能变通实现了.
alter proc [p_met_cost]
as
if exists(select * from tempdb.dbo.sysobjects where id=object_id(N'tempdb.dbo.#Table_met_cost'))
drop table #Table_met_cost
create table #Table_met_cost
(
GID varchar(50),
Batch varchar(50),
GState varchar(50),
CostPrice Decimal(18,4)
)
insert into #Table_met_cost select GID,Batch,GState,CostPrice from v_Goods_Cost
--计算每个制令单从表中每条数据的具体成本 CostPrice总单据总成本 SinCostPrice 单条从表数据的成本
select A.*,isnull(B.Number,0) as Number,A.MScale*A.MCostPrice as CostPrice,
SinCostPrice= case isnull(B.Number,0) when 0 then 0 else A.MScale*A.MCostPrice/B.Number end from
(
---***联合制令单
select a.MOID,a.GID,a.GName,a.Batch,a.Model,a.Unit,a.MScale,isnull(b.CostPrice,0) as MCostPrice,
(select Date from Produce_MadeOrder C where C.MOID=a.MOID) as Date from v_Produce_MadeOrderDetail A left join (
--聚合生产批次成本
select A.Batch,sum(A.CostPrice) as CostPrice from (
--按生产批次求出每条物料投入从表数据的成本(存在问题,如果物料是自制的,则会读取错误)
select (select Batch from v_Cost_MetCost where MCID=A.MCID) as Batch,CostPrice=Number*
isnull(isnull((select top 1 CostPrice from #Table_met_cost
where GID=A.GID and Batch=A.Batch and GState=A.GState),(select top 1 CostPrice from Storage_KCAccount
where GID=A.GID and Batch=A.Batch and GState=A.GState)),0)
from Cost_MetCostDetail A
--
) A Group by A.Batch
--
) B on A.Batch=B.Batch
---***
) A left join v_pro_number B on A.Batch=B.Batch and A.GID=B.GID and B.GState='9002'
CREATE PROC p_test
as
SELECT GETDATE() a
go
CREATE VIEW v_test
AS
SELECT * FROM OPENROWSET('SQLOLEDB.1','server=***;uid=sa;pwd=***','exec db.dbo.p_test')
go
SELECT * FROM v_test