Problem creating function-incorrect syntax error

  • dean gross

    SSCrazy

    Points: 2632

    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

  • Elliott Whitlow

    SSC Guru

    Points: 102296

    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 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply