September 28, 2009 at 4:43 pm
When I try the following I get this invalid syntax error
Msg 156, Level 15, State 1, Procedure udf_OHBudgetsByPeriod_TAB, Line 32
Incorrect syntax near the keyword 'BEGIN'.
I can't see what I'm doing wrong. Could someone please educate me. Thanks
CREATE FUNCTION dbo.udf_OHBudgetsByPeriod_TAB (
@Library char(6) -- PRISM Library (database)
, @FinYr char(4) -- Financial Year
) RETURNS TABLE (
Job_Number char (6)--
, WBS_Number char(5)--
, Cost_Code char(4) --
, Cost_Year char(4)
, Period tinyint--
, Budget decimal (18,4)
)
/*
* description goes here
*
* Related Functions:
* Attribution: Based on xxx by yyy found in zzzzzzzzzzzzz
* Maintenace Notes:
* Example:
SELECT * FROM dbo.udf_OHBudgetsByPeriod_TAB(parfls, 2009 )
* Test Script: TEST_udf_OHBudgetsByPeriod_TAB
* History:
* When Who Description
* ------------- ------- -----------------------------------------
* 2009-09-29 DLG Initial Coding
****************************************************************/
AS
BEGIN
--DECLARE
IF @Library = 'PARFLS'
--BEGIN
SELECT Job_Number, WBS_Number, Cost_Code,Cost_Year,Period, Budget
FROM
(SELECT Job_Number, WBS_Number, Cost_Code, Cost_Year
, SUM(Revised_Amount_Period_01) AS "1"
, SUM(Revised_Amount_Period_02) AS "2"
, SUM(Revised_Amount_Period_03) AS "3"
, SUM(Revised_Amount_Period_04) AS "4"
, SUM(Revised_Amount_Period_05) AS "5"
, SUM(Revised_Amount_Period_06) AS "6"
, SUM(Revised_Amount_Period_07) AS "7"
, SUM(Revised_Amount_Period_08) AS "8"
, SUM(Revised_Amount_Period_09) AS "9"
, SUM(Revised_Amount_Period_10) AS "10"
, SUM(Revised_Amount_Period_11) AS "11"
, SUM(Revised_Amount_Period_12) AS "12"
FROM PARFLS.dbo.JobCostBudgetMaster AS BM
WHERE Cost_Year = @FinYr
GROUP BY Job_Number, WBS_Number, Cost_Code, Cost_Year) AS p
UNPIVOT (Budget for Period IN ("1","2", "3", "4", "5", "6", "7", "8","9","10","11", "12")) as unpiv
--RETURN
--END
IF @Library = 'MWCFLS'
--BEGIN
SELECT Job_Number, WBS_Number, Cost_Code,Cost_Year,Period, Budget
FROM
(SELECT Job_Number, WBS_Number, Cost_Code, Cost_Year
, SUM(Revised_Amount_Period_01) AS "1"
, SUM(Revised_Amount_Period_02) AS "2"
, SUM(Revised_Amount_Period_03) AS "3"
, SUM(Revised_Amount_Period_04) AS "4"
, SUM(Revised_Amount_Period_05) AS "5"
, SUM(Revised_Amount_Period_06) AS "6"
, SUM(Revised_Amount_Period_07) AS "7"
, SUM(Revised_Amount_Period_08) AS "8"
, SUM(Revised_Amount_Period_09) AS "9"
, SUM(Revised_Amount_Period_10) AS "10"
, SUM(Revised_Amount_Period_11) AS "11"
, SUM(Revised_Amount_Period_12) AS "12"
FROM MWCFLS.dbo.JobCostBudgetMaster AS BM
WHERE Cost_Year = @FinYr
GROUP BY Job_Number, WBS_Number, Cost_Code, Cost_Year) AS p
UNPIVOT (Budget for Period IN ("1","2", "3", "4", "5", "6", "7", "8","9","10","11", "12")) as unpiv
RETURN
--END
END
GO
GRANT SELECT ON [dbo].[udf_OHBudgetsByPeriod_TAB] TO [PUBLIC]
GO
September 28, 2009 at 5:50 pm
Here is your problem:
) RETURNS TABLE (
It needs to include a variable name:
) RETURNS @ReturnValue TABLE (
You need to insert the records into that variable..
I can't remember if you need to say
RETURN @ReturnValue
Or if just the return is ok..
CEWII
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply