troubleshooting Question

UDF - passing GETDATE() when calling function

Avatar of metropia
metropiaFlag for United States of America asked on
SQL
25 Comments2 Solutions654 ViewsLast Modified:
Hi,

I would like to make this question, short and clear, I would struggle to do this, but please bear with me. I am in need of help, stumbling with this problem for more than two days and I do not seem to make any advances.

I am trying to call a UDF that returns a table, by passing GETDATE() as the argument.

i.e.

select * from [dbo].[Test] (getdate())

When I do this, I get an error: Line 1: Incorrect syntax near '('.

There are several variables that need the date value from get date
I have tested each variable by hard-coding the value.
I have tested each part that makes up the entire function.

This piece of code that I am including, is the function being tested using hard-coded date values, year value, month number value, and no passing parameter:

GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[Alex_Test] ()


RETURNS @Alex_Test TABLE
	(
		 ProductCode varchar(10)
		,ProductDescription varchar(50)
		,TotalActualShipFeet DECIMAL(14,4)
		,TotalActualShip$$ DECIMAL(14,4)
	)	
AS
BEGIN

DECLARE @startFiscalMonth datetime,
		@endFiscalMonth datetime,
		@fiscalYearNumber int,
		@fiscalMonthNumber int,
		@currentDateMinusOne datetime
		
SELECT	
		@startFiscalMonth = '2012-07-02', 
		@endFiscalMonth = '2012-08-05',
		@fiscalYearNumber = 2013,
		@fiscalMonthNumber = 4,
		@currentDateMinusOne = '2012-07-10'

INSERT INTO @Alex_Test

SELECT 
	 actualshipments.[Product_Code]
	,actualshipments.[Description]
	,actualshipments.[TotalActualShipFeet]
	,actualshipments.[TotalActualShip$$]
FROM 
(
	----------------------
	-- ACTUAL SHIPMENTS --
	----------------------
	SELECT top 100 percent
	-----------
	-- COL 1 --
	-----------
	 col.PRODUCT_CODE AS PRODUCT_CODE	
	-----------
	-- COL 2 --
	-----------
	,p.DESCRIPTION AS DESCRIPTION
	-----------
	-- COL 3 --
	-----------
	,SUM((CASE
			WHEN col.PRODUCT_CODE IN ('8500', '9000') THEN 0
			ELSE
				CASE
					WHEN RG_SALESFORECASTBYPRODUCT_WEBSTER.FiscalYear = @fiscalYearNumber
						AND RG_SALESFORECASTBYPRODUCT_WEBSTER.FiscalMonth = @fiscalMonthNumber
						THEN
							(CASE WHEN shl.SHIPPING_UM = 'FT' THEN shl.SHIPPED_QTY ELSE 0 END )
				End
		END)) AS 'TotalActualShipFeet'
	-----------
	-- COL 4 --
	-----------
	,SUM((CASE
		WHEN col.PRODUCT_CODE IN ('8500', '9000') THEN 0
		ELSE
			CASE
				WHEN RG_SALESFORECASTBYPRODUCT_WEBSTER.FiscalYear = @fiscalYearNumber
					AND RG_SALESFORECASTBYPRODUCT_WEBSTER.FiscalMonth = @fiscalMonthNumber
					THEN ROUND((shl.SHIPPED_QTY * shl.UNIT_PRICE), 4)
				ELSE 0
			END
		END)) AS 'TotalActualShip$$'		
	FROM dbo.SHIPPER AS sh
	INNER JOIN dbo.SHIPPER_LINE AS shl ON sh.CUST_ORDER_ID = shl.CUST_ORDER_ID AND sh.PACKLIST_ID = shl.PACKLIST_ID
	INNER JOIN dbo.CUST_ORDER_LINE AS col ON shl.CUST_ORDER_ID = col.CUST_ORDER_ID AND shl.CUST_ORDER_LINE_NO = col.LINE_NO
	INNER JOIN dbo.PRODUCT AS p ON col.PRODUCT_CODE = p.CODE
	INNER JOIN dbo.RG_SALESFORECASTBYPRODUCT_WEBSTER AS RG_SALESFORECASTBYPRODUCT_WEBSTER ON p.[DESCRIPTION] = RG_SALESFORECASTBYPRODUCT_WEBSTER.ProductCode
	WHERE sh.SHIPPED_DATE >= @startFiscalMonth AND sh.SHIPPED_DATE <= @endFiscalMonth
	GROUP BY col.PRODUCT_CODE, p.DESCRIPTION
) as actualshipments

	RETURN 
END


This code I'm including, is using a date parameter passed to the function to later be used on the variable assignment:

GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[Alex_Test] (@dt DATETIME)


RETURNS @Alex_Test TABLE
	(
		 ProductCode varchar(10)
		,ProductDescription varchar(50)
		,TotalActualShipFeet DECIMAL(14,4)
		,TotalActualShip$$ DECIMAL(14,4)
	)	
AS
BEGIN

DECLARE @startFiscalMonth datetime,
		@endFiscalMonth datetime,
		@fiscalYearNumber int,
		@fiscalMonthNumber int
		
SELECT	@startFiscalMonth = dbo.RG_GetFiscalMonthStart(@dt), 
		@endFiscalMonth = dbo.RG_GetFiscalMonthEnd(@dt),
		@fiscalYearNumber = dbo.RG_GetFiscalYearName(@dt),
		@fiscalMonthNumber = dbo.RG_GetFiscalMonthKeyFiscal(@dt)

INSERT INTO @Alex_Test

SELECT 
	 actualshipments.[Product_Code]
	,actualshipments.[Description]
	,actualshipments.[TotalActualShipFeet]
	,actualshipments.[TotalActualShip$$]
FROM 
(
	----------------------
	-- ACTUAL SHIPMENTS --
	----------------------
	SELECT top 100 percent
	-----------
	-- COL 1 --
	-----------
	 col.PRODUCT_CODE AS PRODUCT_CODE	
	-----------
	-- COL 2 --
	-----------
	,p.DESCRIPTION AS DESCRIPTION
	-----------
	-- COL 3 --
	-----------
	,SUM((CASE
			WHEN col.PRODUCT_CODE IN ('8500', '9000') THEN 0
			ELSE
				CASE
					WHEN RG_SALESFORECASTBYPRODUCT_WEBSTER.FiscalYear = @fiscalYearNumber
						AND RG_SALESFORECASTBYPRODUCT_WEBSTER.FiscalMonth = @fiscalMonthNumber
						THEN
							(CASE WHEN shl.SHIPPING_UM = 'FT' THEN shl.SHIPPED_QTY ELSE 0 END )
				End
		END)) AS 'TotalActualShipFeet'
	-----------
	-- COL 4 --
	-----------
	,SUM((CASE
		WHEN col.PRODUCT_CODE IN ('8500', '9000') THEN 0
		ELSE
			CASE
				WHEN RG_SALESFORECASTBYPRODUCT_WEBSTER.FiscalYear = @fiscalYearNumber
					AND RG_SALESFORECASTBYPRODUCT_WEBSTER.FiscalMonth = @fiscalMonthNumber
					THEN ROUND((shl.SHIPPED_QTY * shl.UNIT_PRICE), 4)
				ELSE 0
			END
		END)) AS 'TotalActualShip$$'		
	FROM dbo.SHIPPER AS sh
	INNER JOIN dbo.SHIPPER_LINE AS shl ON sh.CUST_ORDER_ID = shl.CUST_ORDER_ID AND sh.PACKLIST_ID = shl.PACKLIST_ID
	INNER JOIN dbo.CUST_ORDER_LINE AS col ON shl.CUST_ORDER_ID = col.CUST_ORDER_ID AND shl.CUST_ORDER_LINE_NO = col.LINE_NO
	INNER JOIN dbo.PRODUCT AS p ON col.PRODUCT_CODE = p.CODE
	INNER JOIN dbo.RG_SALESFORECASTBYPRODUCT_WEBSTER AS RG_SALESFORECASTBYPRODUCT_WEBSTER ON p.[DESCRIPTION] = RG_SALESFORECASTBYPRODUCT_WEBSTER.ProductCode
	WHERE sh.SHIPPED_DATE >= @startFiscalMonth AND sh.SHIPPED_DATE <= @endFiscalMonth
	GROUP BY col.PRODUCT_CODE, p.DESCRIPTION
) as actualshipments

	RETURN 
END


The second version is the one that returns the error.

I am using sql server 2000, and I tested the same code in sql server 2008, and I do not receive that error, in that version of sql, the function runs fine.

Thank you for all your help on this matter.
ASKER CERTIFIED SOLUTION
Vadim Rapp

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 25 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 25 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros