昨天经Dawugui、灵魂等众高手提示,用传参的方法将GetDate()传进去,以及调用,但是怎么调就怎么出错?
问题:以GETDATE()传递的方式如何调用自定义函数
大乌龟的思路是:
将Create的时候用CREATE FUNCTION f_Cost(@FBillNo VARCHAR(40),@dt VARCHAR(10))
--或者
CREATE FUNCTION f_Cost(@FBillNo VARCHAR(40),@dt VARCHAR(19))
/*但这不可行,因为偶的自定义函数里有
Day(@dt),DateAdd(@dt) 这个@dt必须是DateTime或是SmallDateTime
*/
灵魂的思路是:
将Create的时候用CREATE FUNCTION f_Cost(@FBillNo VARCHAR(40),@dt DateTime)
/*问题所在,如何调用函数?
--这种写法查询分析器就报错:说GETDATE()前面有语法报错,
SELECT * FROM f_Cost('WORK025933',GETDATE())
--这种写法也是一样的说SELECT前有语法错误
SELECT * FROM f_Cost('WORK025933',SELECT GETDATE())
--如何调用自定义函数?
原代码如下:-->成本函数(查询以单据编号)
CREATE FUNCTION f_Cost(@FBillNo VARCHAR(40),@dt DATETIME)
RETURNS @t2 TABLE(FTranType VARCHAR(40),FBillNo VARCHAR(40),FItemID VARCHAR(40),FAuxStockQty DECIMAL(20,2),层次 VARCHAR(40))
AS
BEGIN
DECLARE @t INT
SET @t=1
INSERT @t2
SELECT [单据类型]=CASE WHEN a.FTranType=85 THEN '厂内生产任务单' WHEN a.FTranType=571 THEN '委外生产任务单' END,
[生产任务单号]=a.FBillNo,[材料编码]=b.FItemID,[投料数量]=ISNULL(b.FAuxStockQty,0),[层次]=@t
FROM ICMO a
LEFT OUTER JOIN PPBomEntry b ON a.FInterID=b.FICMOInterID
WHERE a.FBillNo=@FBillNo AND a.FStatus='3'
AND a.FCheckDate>=CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),@dt,120)+'-28' ELSE CONVERT(VARCHAR(7),DATEADD(MONTH,-1,@dt),120)+'-28' END
AND a.FCheckDate<CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),DATEADD(MONTH,1,@dt),120)+'-28' ELSE CONVERT(VARCHAR(7),@dt,120)+'-28' END
DECLARE @t3 TABLE(FTranType VARCHAR(40),FBillNo VARCHAR(40),FItemID VARCHAR(40),FAuxStockQty DECIMAL(20,2),层次 VARCHAR(40))
INSERT @t3
SELECT [单据类型]=a.FTranType,[生产任务单号]=a.FBillNo,[材料编码]=a.FItemID,[投料数量]=ISNULL(a.FAuxStockQty,0),[层次]=a.层次
FROM @t2 a
WHERE a.FItemID IN (SELECT b.FItemID FROM PPBom b LEFT OUTER JOIN PPBomEntry c ON b.FInterID=c.FInterID WHERE b.FDate>='2009-11-28' AND b.FDate<='2009-12-27' )
WHILE @@ROWCOUNT>0
BEGIN
SET @t=@t+1
INSERT @t2
SELECT a.FTranType,a.FBillNo,c.FItemID,[投料数量]=CASE WHEN SUM(c.FAuxStockQty)=0 THEN SUM(a.FAuxStockQty*c.FAuxStockQty)/COUNT(a.FItemID) ELSE SUM(ISNULL(c.FAuxStockQty,0))*SUM(a.FAuxStockQty)/COUNT(a.FItemID)/SUM(d.FAuxStockQty) END,@t
FROM @t3 a
LEFT OUTER JOIN PPBOM b ON a.FItemID=b.FItemID
LEFT OUTER JOIN PPBOMEntry c ON b.FInterID=c.FInterID
LEFT OUTER JOIN ICMO d ON b.FICMOInterID=d.FInterID
WHERE
b.FDate>=CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),@dt,120)+'-28' ELSE CONVERT(VARCHAR(7),DATEADD(MONTH,-1,@dt),120)+'-28' END
AND b.FDate<CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),DATEADD(MONTH,1,@dt),120)+'-28' ELSE CONVERT(VARCHAR(7),@dt,120)+'-28' END
AND d.fStatus='3' AND a.层次=@t-1
GROUP BY a.FTranType,a.FBillNo,c.FItemID
INSERT @t3
SELECT a.FTranType,a.FBillNo,a.FItemID,a.FAuxStockQty,a.层次
FROM @t2 a
WHERE a.层次=@t AND a.FItemID IN
(SELECT b.FItemID FROM PPBom b LEFT OUTER JOIN PPBomEntry c ON b.FInterID=c.FInterID LEFT OUTER JOIN ICMO d ON b.FICMOInterID=d.FInterID
WHERE
b.FDate>=CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),@dt,120)+'-28' ELSE CONVERT(VARCHAR(7),DATEADD(MONTH,-1,@dt),120)+'-28' END
AND b.FDate<CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),DATEADD(MONTH,1,@dt),120)+'-28' ELSE CONVERT(VARCHAR(7),@dt,120)+'-28' END
AND d.FStatus='3')
END
DELETE FROM @t2
WHERE FItemID IN (SELECT FItemID FROM @t3)
RETURN
END
drop function f_cost
drop proc sp_f_cost
-->成本存储过程(查询以单据编号)
CREATE PROC sp_f_cost(@FBillNo VARCHAR(40))
AS
BEGIN
SELECT [单据类型]=a.FTranType,[单据编号]=a.FBillNo,[成本]=SUM(a.FAuxStockQty*CASE WHEN c.FBegQty=0 OR c.FBegQty IS NULL THEN 0 ELSE c.FBegBal/c.FBegQty END)
FROM f_Cost(@FBillNo,'GETDATE()')a
LEFT OUTER JOIN t_ICItem b ON a.FItemID=b.FItemID
LEFT OUTER JOIN
(SELECT b.FItemID,b.FPeriod,a.FStockID,a.FBegQty,a.FBegBal
FROM
(SELECT FItemID,[FPeriod]=MAX(FPeriod) FROM ICInvInitial WHERE FStockID<>'1365' GROUP BY FItemID) b
LEFT OUTER JOIN ICInvInitial a ON a.FItemID=b.FItemID AND a.FPeriod=b.FPeriod
WHERE a.FStockID<>'1365' )
c ON a.FItemID=c.FItemID
GROUP BY a.FTranType,a.FBillNo
END
-->成本自定义函数与成本存储过程测试
SELECT * FROM f_cost('WORK025933',GETDATE())
EXEC sp_f_cost @FBillNo='WORK025933'
问题:以GETDATE()传递的方式如何调用自定义函数
大乌龟的思路是:
将Create的时候用CREATE FUNCTION f_Cost(@FBillNo VARCHAR(40),@dt VARCHAR(10))
--或者
CREATE FUNCTION f_Cost(@FBillNo VARCHAR(40),@dt VARCHAR(19))
/*但这不可行,因为偶的自定义函数里有
Day(@dt),DateAdd(@dt) 这个@dt必须是DateTime或是SmallDateTime
*/
灵魂的思路是:
将Create的时候用CREATE FUNCTION f_Cost(@FBillNo VARCHAR(40),@dt DateTime)
/*问题所在,如何调用函数?
--这种写法查询分析器就报错:说GETDATE()前面有语法报错,
SELECT * FROM f_Cost('WORK025933',GETDATE())
--这种写法也是一样的说SELECT前有语法错误
SELECT * FROM f_Cost('WORK025933',SELECT GETDATE())
--如何调用自定义函数?
原代码如下:-->成本函数(查询以单据编号)
CREATE FUNCTION f_Cost(@FBillNo VARCHAR(40),@dt DATETIME)
RETURNS @t2 TABLE(FTranType VARCHAR(40),FBillNo VARCHAR(40),FItemID VARCHAR(40),FAuxStockQty DECIMAL(20,2),层次 VARCHAR(40))
AS
BEGIN
DECLARE @t INT
SET @t=1
INSERT @t2
SELECT [单据类型]=CASE WHEN a.FTranType=85 THEN '厂内生产任务单' WHEN a.FTranType=571 THEN '委外生产任务单' END,
[生产任务单号]=a.FBillNo,[材料编码]=b.FItemID,[投料数量]=ISNULL(b.FAuxStockQty,0),[层次]=@t
FROM ICMO a
LEFT OUTER JOIN PPBomEntry b ON a.FInterID=b.FICMOInterID
WHERE a.FBillNo=@FBillNo AND a.FStatus='3'
AND a.FCheckDate>=CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),@dt,120)+'-28' ELSE CONVERT(VARCHAR(7),DATEADD(MONTH,-1,@dt),120)+'-28' END
AND a.FCheckDate<CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),DATEADD(MONTH,1,@dt),120)+'-28' ELSE CONVERT(VARCHAR(7),@dt,120)+'-28' END
DECLARE @t3 TABLE(FTranType VARCHAR(40),FBillNo VARCHAR(40),FItemID VARCHAR(40),FAuxStockQty DECIMAL(20,2),层次 VARCHAR(40))
INSERT @t3
SELECT [单据类型]=a.FTranType,[生产任务单号]=a.FBillNo,[材料编码]=a.FItemID,[投料数量]=ISNULL(a.FAuxStockQty,0),[层次]=a.层次
FROM @t2 a
WHERE a.FItemID IN (SELECT b.FItemID FROM PPBom b LEFT OUTER JOIN PPBomEntry c ON b.FInterID=c.FInterID WHERE b.FDate>='2009-11-28' AND b.FDate<='2009-12-27' )
WHILE @@ROWCOUNT>0
BEGIN
SET @t=@t+1
INSERT @t2
SELECT a.FTranType,a.FBillNo,c.FItemID,[投料数量]=CASE WHEN SUM(c.FAuxStockQty)=0 THEN SUM(a.FAuxStockQty*c.FAuxStockQty)/COUNT(a.FItemID) ELSE SUM(ISNULL(c.FAuxStockQty,0))*SUM(a.FAuxStockQty)/COUNT(a.FItemID)/SUM(d.FAuxStockQty) END,@t
FROM @t3 a
LEFT OUTER JOIN PPBOM b ON a.FItemID=b.FItemID
LEFT OUTER JOIN PPBOMEntry c ON b.FInterID=c.FInterID
LEFT OUTER JOIN ICMO d ON b.FICMOInterID=d.FInterID
WHERE
b.FDate>=CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),@dt,120)+'-28' ELSE CONVERT(VARCHAR(7),DATEADD(MONTH,-1,@dt),120)+'-28' END
AND b.FDate<CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),DATEADD(MONTH,1,@dt),120)+'-28' ELSE CONVERT(VARCHAR(7),@dt,120)+'-28' END
AND d.fStatus='3' AND a.层次=@t-1
GROUP BY a.FTranType,a.FBillNo,c.FItemID
INSERT @t3
SELECT a.FTranType,a.FBillNo,a.FItemID,a.FAuxStockQty,a.层次
FROM @t2 a
WHERE a.层次=@t AND a.FItemID IN
(SELECT b.FItemID FROM PPBom b LEFT OUTER JOIN PPBomEntry c ON b.FInterID=c.FInterID LEFT OUTER JOIN ICMO d ON b.FICMOInterID=d.FInterID
WHERE
b.FDate>=CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),@dt,120)+'-28' ELSE CONVERT(VARCHAR(7),DATEADD(MONTH,-1,@dt),120)+'-28' END
AND b.FDate<CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),DATEADD(MONTH,1,@dt),120)+'-28' ELSE CONVERT(VARCHAR(7),@dt,120)+'-28' END
AND d.FStatus='3')
END
DELETE FROM @t2
WHERE FItemID IN (SELECT FItemID FROM @t3)
RETURN
END
drop function f_cost
drop proc sp_f_cost
-->成本存储过程(查询以单据编号)
CREATE PROC sp_f_cost(@FBillNo VARCHAR(40))
AS
BEGIN
SELECT [单据类型]=a.FTranType,[单据编号]=a.FBillNo,[成本]=SUM(a.FAuxStockQty*CASE WHEN c.FBegQty=0 OR c.FBegQty IS NULL THEN 0 ELSE c.FBegBal/c.FBegQty END)
FROM f_Cost(@FBillNo,'GETDATE()')a
LEFT OUTER JOIN t_ICItem b ON a.FItemID=b.FItemID
LEFT OUTER JOIN
(SELECT b.FItemID,b.FPeriod,a.FStockID,a.FBegQty,a.FBegBal
FROM
(SELECT FItemID,[FPeriod]=MAX(FPeriod) FROM ICInvInitial WHERE FStockID<>'1365' GROUP BY FItemID) b
LEFT OUTER JOIN ICInvInitial a ON a.FItemID=b.FItemID AND a.FPeriod=b.FPeriod
WHERE a.FStockID<>'1365' )
c ON a.FItemID=c.FItemID
GROUP BY a.FTranType,a.FBillNo
END
-->成本自定义函数与成本存储过程测试
SELECT * FROM f_cost('WORK025933',GETDATE())
EXEC sp_f_cost @FBillNo='WORK025933'
SELECT * FROM f_cost('WORK025933',GETDATE())
-->
declare @dt varchar(10)
set @dt = convert(varchar(10),getdate(),120)SELECT * FROM f_cost('WORK025933',@dt)
RETURNS varchar(10)
AS
BEGIN
return @DATE
end
godeclare @dt varchar(10)
set @dt = convert(varchar(10),getdate(),120)select dbo.f_getdate(@dt)drop function f_getdate/*
----------
2010-01-13(所影响的行数为 1 行)
*/就是你的参数同样不能用getdate(),得先把getdate()赋到一个变量中,然后使用这个变量做为参数传进去.
USE TEMPDB
GO
IF OBJECT_ID('FUN_MU') IS NOT NULL DROP FUNCTION FUN_MU
GO
CREATE FUNCTION FUN_MU(@COL1 VARCHAR(10),@DATE DATETIME)
RETURNS @T TABLE(COL1 VARCHAR(50))
AS
BEGIN
INSERT INTO @T
SELECT CONVERT(VARCHAR(10),@DATE,120) UNION ALL
SELECT CONVERT(VARCHAR(10),@DATE,112)
RETURN
END
GO
--我这怎么没问题
SELECT * FROM DBO.FUN_MU('1',GETDATE())
/*
2010-01-13
20100113
*/
╮(╯▽╰)╭ 是啊发现我的也没问题啊??
alter FUNCTION f_getdate (@r nvarchar(10),@DATE datetime)
RETURNS datetime
AS
BEGIN
return @DATE
end
goselect dbo.f_getdate('dsf',getdate())
/*
(1 行受影响)
2010-01-13 10:20:47.740
*/