Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

UDF - passing GETDATE() when calling function

Avatar of metropia
metropiaFlag for United States of America asked on
SQL
25 Comments1 Solution654 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
Avatar of Vadim Rapp
Vadim RappFlag of United States of America image

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

Commented:
This problem has been solved!
Unlock 1 Answer and 25 Comments.
See Answers