none
Computed Column - Error Validating Formula RRS feed

  • Question

  • Hello,

    I have a table called EmployeeJOURNAL where I keep records regarding the employees attendance.
    The columns of the table are EmployeeID(int), JournalMonth(int), JournalYear(int), D1 (nvarchar,3), D2 (nvarchar,3) .... D31(nvarchar,3) where D1 to D31 are the days of the month and the value saved should be P - for Present, AL for Annual Leave, SL for Sick Leave etc.
    Inside this table I want to have a computed column that should calculate Worked Hours as follows:
     - for each day that has P - if the day is a Friday, add 4.5 hours
                                          - if the day is different from Friday, add 7.5 hours
    I managed to create a user defined function, i did some tests and it returns the right values BUT when I try to attache the function to the column I get the following error: Error validating the formula for column ....
    I forgot to say that I am using SQL Server 2005

    Here is the function:

    CREATE

     

     FUNCTION [dbo].[UDF_P_Hours]( @Month as int,

    @Year

    as int,

    @D1

    as nvarchar(3),

    @D2

    as nvarchar(3),

    @D3

    as nvarchar(3),

    @D4

    as nvarchar(3),

    @D5

    as nvarchar(3),

    @D6

    as nvarchar(3),

    @D7

    as nvarchar(3),

    @D8

    as nvarchar(3),

    @D9

    as nvarchar(3),

    @D10

    as nvarchar(3),

    @D11

    as nvarchar(3),

    @D12

    as nvarchar(3),

    @D13

    as nvarchar(3),

    @D14

    as nvarchar(3),

    @D15

    as nvarchar(3),

    @D16

    as nvarchar(3),

    @D17

    as nvarchar(3),

    @D18

    as nvarchar(3),

    @D19

    as nvarchar(3),

    @D20

    as nvarchar(3),

    @D21

    as nvarchar(3),

    @D22

    as nvarchar(3),

    @D23

    as nvarchar(3),

    @D24

    as nvarchar(3),

    @D25

    as nvarchar(3),

    @D26

    as nvarchar(3),

    @D27

    as nvarchar(3),

    @D28

    as nvarchar(3),

    @D29

    as nvarchar(3),

    @D30

    as nvarchar(3),

    @D31

    as nvarchar(3))

    RETURNS

     

    float

    AS

    BEGIN

     

    DECLARE @PHours as float

     

    DECLARE @NumberOfDays as int

     

    DECLARE @Day as nvarchar(4)

     

    DECLARE @i as int

     

    DECLARE @Date as datetime

     

    DECLARE @StringDate as nvarchar(20)

     

    DECLARE @DayOfTheWeek as int

     

    DECLARE @SQL nvarchar(2000)

     

    SET @PHours = 0.0

     

    SET @i = 1

     

    IF @Month in (1, 3, 5, 7, 8, 10, 12)

     

    BEGIN

     

    SET @NumberOfDays=31

     

    END

     

    IF @Month in (4, 6, 9, 11)

     

    BEGIN

     

    SET @NumberOfDays=30

     

    END

     

    IF @Month=2

     

    BEGIN

     

    IF @Year in ( 2008, 2012, 2016, 2020, 2024)

     

    begin

     

    SET @NumberOfDays=29

     

    end

     

    ELSE

     

    begin

     

    SET @NumberOfDays=28

     

    END

     

    END

    WHILE

     

    @i <= @NumberOfDays

     

    BEGIN

     

    SET @Day = '@D' + convert(nvarchar, @i)

     

    SET @StringDate = convert(nvarchar,@Year) + '-'+ convert(nvarchar,@Month) + '-'+ convert(nvarchar,@i)

     

    SET @Date= convert(datetime, @StringDate, 102)

     

    SET @DayOfTheWeek = DATEPART(dw, @Date)

     

    SET @SQL = ' IF ' + @Day + ' = ''P''

    BEGIN

    IF @DayOfTheWeek = 6 -- On a Friday

    BEGIN

    SET @PHours = @PHours + 4.5

    END

    ELSE

    BEGIN

    SET @PHours = @PHours + 7.5

    END

    END'

     

    EXEC sp_executesql @SQL, N'@D1 nvarchar(3),

    @D2 nvarchar(3),

    @D3 nvarchar(3),

    @D4 nvarchar(3),

    @D5 nvarchar(3),

    @D6 nvarchar(3),

    @D7 nvarchar(3),

    @D8 nvarchar(3),

    @D9 nvarchar(3),

    @D10 nvarchar(3),

    @D11 nvarchar(3),

    @D12 nvarchar(3),

    @D13 nvarchar(3),

    @D14 nvarchar(3),

    @D15 nvarchar(3),

    @D16 nvarchar(3),

    @D17 nvarchar(3),

    @D18 nvarchar(3),

    @D19 nvarchar(3),

    @D20 nvarchar(3),

    @D21 nvarchar(3),

    @D22 nvarchar(3),

    @D23 nvarchar(3),

    @D24 nvarchar(3),

    @D25 nvarchar(3),

    @D26 nvarchar(3),

    @D27 nvarchar(3),

    @D28 nvarchar(3),

    @D29 nvarchar(3),

    @D30 nvarchar(3),

    @D31 nvarchar(3),

    @DayOfTheWeek int,

    @PHours float OUTPUT'

     

    ,

    @D1

    , @D2, @D3, @D4, @D5, @D6, @D7, @D8, @D9, @D10,

    @D11

    , @D12, @D13, @D14, @D15, @D16, @D17, @D18, @D19, @D20,

    @D21

    , @D22, @D23, @D24, @D25, @D26, @D27, @D28, @D29, @D30, @D31,

    @DayOfTheWeek

    , @PHours OUTPUT

     

    SET @i= @i + 1

     

    END

     

    -- Return the result of the function

    RETURN

     

    @PHours

    END


    and here is how I try to link it to the column(Computed Columns Specification - Formula):

    [dbo].[UDF_P_Hours]([JMonth],[JYear],[D1],[D2],[D3],[D4],[D5],[D6],[D7],[D8],[D9],[D10],[D11],[D12],[D13],[D14],[D15],[D16],[D17],[D18],[D19],[D20],[D21],[D22],[D23],[D24],[D25],[D26],[D27],[D28],[D29],[D30],[D31])


    What do I do wrong?





    Saturday, February 27, 2010 10:56 AM

Answers

  • Dynamic SQL is not allowed within user-defined function. Can you rewrite it without the dynamic SQL? Demo follows:

    CREATE FUNCTION udfDynamicSQL ( @i int)
    RETURNS INT
    AS
    BEGIN
    DECLARE @SQL nvarchar(max) = 'SELECT @i * @i'
    EXEC sp_executeSQL @SQL
    RETURN (@i*@i*@i)
    
    END
    GO
    SELECT dbo.udfDynamicSQL(7)
    /*
    Msg 557, Level 16, State 2, Line 1
    Only functions and some extended stored procedures can be executed from 
    within a function.
    */



    After fixing it, check out the following. The UDF is used to create a computed column CompColAlpha in  test table Alpha. It compiles OK.

    -- CREATE UDF
    CREATE FUNCTION [dbo].[udf_p_hours] 
                   (@Month AS INT, 
                    @Year  AS INT, 
                    @D1    AS NVARCHAR(3), 
                    @D2    AS NVARCHAR(3), 
                    @D3    AS NVARCHAR(3), 
                    @D4    AS NVARCHAR(3), 
                    @D5    AS NVARCHAR(3), 
                    @D6    AS NVARCHAR(3), 
                    @D7    AS NVARCHAR(3), 
                    @D8    AS NVARCHAR(3), 
                    @D9    AS NVARCHAR(3), 
                    @D10   AS NVARCHAR(3), 
                    @D11   AS NVARCHAR(3), 
                    @D12   AS NVARCHAR(3), 
                    @D13   AS NVARCHAR(3), 
                    @D14   AS NVARCHAR(3), 
                    @D15   AS NVARCHAR(3), 
                    @D16   AS NVARCHAR(3), 
                    @D17   AS NVARCHAR(3), 
                    @D18   AS NVARCHAR(3), 
                    @D19   AS NVARCHAR(3), 
                    @D20   AS NVARCHAR(3), 
                    @D21   AS NVARCHAR(3), 
                    @D22   AS NVARCHAR(3), 
                    @D23   AS NVARCHAR(3), 
                    @D24   AS NVARCHAR(3), 
                    @D25   AS NVARCHAR(3), 
                    @D26   AS NVARCHAR(3), 
                    @D27   AS NVARCHAR(3), 
                    @D28   AS NVARCHAR(3), 
                    @D29   AS NVARCHAR(3), 
                    @D30   AS NVARCHAR(3), 
                    @D31   AS NVARCHAR(3)) 
    RETURNS FLOAT 
    AS 
      BEGIN 
        DECLARE  @PHours  AS FLOAT 
         
        DECLARE  @NumberOfDays  AS INT 
         
        DECLARE  @Day  AS NVARCHAR(4) 
         
        DECLARE  @i  AS INT 
         
        DECLARE  @Date  AS DATETIME 
         
        DECLARE  @StringDate  AS NVARCHAR(20) 
         
        DECLARE  @DayOfTheWeek  AS INT 
         
        DECLARE  @SQL NVARCHAR(2000) 
         
        SET @PHours = 0.0 
         
        SET @i = 1 
         
        IF @Month IN (1,3,5,7, 
                      8,10,12) 
          BEGIN 
            SET @NumberOfDays = 31 
          END 
         
        IF @Month IN (4,6,9,11) 
          BEGIN 
            SET @NumberOfDays = 30 
          END 
         
        IF @Month = 2 
          BEGIN 
            IF @Year IN (2008,2012,2016,2020, 
                         2024) 
              BEGIN 
                SET @NumberOfDays = 29 
              END 
            ELSE 
              BEGIN 
                SET @NumberOfDays = 28 
              END 
          END 
         
        WHILE @i <= @NumberOfDays 
          BEGIN 
            SET @Day = '@D' + Convert(NVARCHAR,@i) 
             
            SET @StringDate = Convert(NVARCHAR,@Year) + '-' + Convert(NVARCHAR,@Month) + '-' + Convert(NVARCHAR,@i) 
             
            SET @Date = Convert(DATETIME,@StringDate,102) 
             
            SET @DayOfTheWeek = Datepart(dw,@Date) 
             
            SET @SQL = ' IF ' + @Day + ' = ''P''  BEGIN IF @DayOfTheWeek = 6 -- On a Friday BEGIN SET @PHours = @PHours + 4.5 END ELSE BEGIN SET @PHours = @PHours + 7.5 END END' 
             
            EXEC Sp_executesql 
              @SQL , 
              N'@D1 nvarchar(3),  @D2 nvarchar(3), @D3 nvarchar(3), @D4 nvarchar(3), @D5 nvarchar(3), @D6 nvarchar(3), @D7 nvarchar(3), @D8 nvarchar(3), @D9 nvarchar(3), @D10 nvarchar(3), @D11 nvarchar(3), @D12 nvarchar(3), @D13 nvarchar(3), @D14 nvarchar(3), @D15 nvarchar(3), @D16 nvarchar(3), @D17 nvarchar(3), @D18 nvarchar(3), @D19 nvarchar(3), @D20 nvarchar(3), @D21 nvarchar(3), @D22 nvarchar(3), @D23 nvarchar(3), @D24 nvarchar(3), @D25 nvarchar(3), @D26 nvarchar(3), @D27 nvarchar(3), @D28 nvarchar(3), @D29 nvarchar(3), @D30 nvarchar(3), @D31 nvarchar(3), @DayOfTheWeek int, @PHours float OUTPUT' , 
              @D1 , 
              @D2 , 
              @D3 , 
              @D4 , 
              @D5 , 
              @D6 , 
              @D7 , 
              @D8 , 
              @D9 , 
              @D10 , 
              @D11 , 
              @D12 , 
              @D13 , 
              @D14 , 
              @D15 , 
              @D16 , 
              @D17 , 
              @D18 , 
              @D19 , 
              @D20 , 
              @D21 , 
              @D22 , 
              @D23 , 
              @D24 , 
              @D25 , 
              @D26 , 
              @D27 , 
              @D28 , 
              @D29 , 
              @D30 , 
              @D31 , 
              @DayOfTheWeek , 
              @PHours OUTPUT 
             
            SET @i = @i + 1 
          END 
         
        -- Return the result of the function 
        RETURN @PHours 
      END 
    
    GO 
    -- CREATE COMPUTED COLUMN USING UDF
    CREATE TABLE Alpha ( 
      Jmonth       INT, 
      JYear        INT, 
      D1           NVARCHAR(3), 
      D2           NVARCHAR(3), 
      D3           NVARCHAR(3), 
      D4           NVARCHAR(3), 
      D5           NVARCHAR(3), 
      D6           NVARCHAR(3), 
      D7           NVARCHAR(3), 
      D8           NVARCHAR(3), 
      D9           NVARCHAR(3), 
      D10          NVARCHAR(3), 
      D11          NVARCHAR(3), 
      D12          NVARCHAR(3), 
      D13          NVARCHAR(3), 
      D14          NVARCHAR(3), 
      D15          NVARCHAR(3), 
      D16          NVARCHAR(3), 
      D17          NVARCHAR(3), 
      D18          NVARCHAR(3), 
      D19          NVARCHAR(3), 
      D20          NVARCHAR(3), 
      D21          NVARCHAR(3), 
      D22          NVARCHAR(3), 
      D23          NVARCHAR(3), 
      D24          NVARCHAR(3), 
      D25          NVARCHAR(3), 
      D26          NVARCHAR(3), 
      D27          NVARCHAR(3), 
      D28          NVARCHAR(3), 
      D29          NVARCHAR(3), 
      D30          NVARCHAR(3), 
      D31          NVARCHAR(3), 
      CompColALPHA AS [dbo].[UDF_P_Hours]([JMonth],[JYear],[D1],[D2],[D3],[D4],[D5],[D6],[D7],[D8],[D9],[D10],[D11],[D12],[D13],[D14],[D15],[D16],[D17],[D18],[D19],[D20],[D21],[D22],[D23],[D24],[D25],[D26],[D27],[D28],[D29],[D30],[D31])); 

    Kalman Toth, SQL Server & BI Training, SSAS OLAP, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Otilia. _ Saturday, February 27, 2010 2:24 PM
    Saturday, February 27, 2010 1:22 PM
    Moderator

All replies

  • Dynamic SQL is not allowed within user-defined function. Can you rewrite it without the dynamic SQL? Demo follows:

    CREATE FUNCTION udfDynamicSQL ( @i int)
    RETURNS INT
    AS
    BEGIN
    DECLARE @SQL nvarchar(max) = 'SELECT @i * @i'
    EXEC sp_executeSQL @SQL
    RETURN (@i*@i*@i)
    
    END
    GO
    SELECT dbo.udfDynamicSQL(7)
    /*
    Msg 557, Level 16, State 2, Line 1
    Only functions and some extended stored procedures can be executed from 
    within a function.
    */



    After fixing it, check out the following. The UDF is used to create a computed column CompColAlpha in  test table Alpha. It compiles OK.

    -- CREATE UDF
    CREATE FUNCTION [dbo].[udf_p_hours] 
                   (@Month AS INT, 
                    @Year  AS INT, 
                    @D1    AS NVARCHAR(3), 
                    @D2    AS NVARCHAR(3), 
                    @D3    AS NVARCHAR(3), 
                    @D4    AS NVARCHAR(3), 
                    @D5    AS NVARCHAR(3), 
                    @D6    AS NVARCHAR(3), 
                    @D7    AS NVARCHAR(3), 
                    @D8    AS NVARCHAR(3), 
                    @D9    AS NVARCHAR(3), 
                    @D10   AS NVARCHAR(3), 
                    @D11   AS NVARCHAR(3), 
                    @D12   AS NVARCHAR(3), 
                    @D13   AS NVARCHAR(3), 
                    @D14   AS NVARCHAR(3), 
                    @D15   AS NVARCHAR(3), 
                    @D16   AS NVARCHAR(3), 
                    @D17   AS NVARCHAR(3), 
                    @D18   AS NVARCHAR(3), 
                    @D19   AS NVARCHAR(3), 
                    @D20   AS NVARCHAR(3), 
                    @D21   AS NVARCHAR(3), 
                    @D22   AS NVARCHAR(3), 
                    @D23   AS NVARCHAR(3), 
                    @D24   AS NVARCHAR(3), 
                    @D25   AS NVARCHAR(3), 
                    @D26   AS NVARCHAR(3), 
                    @D27   AS NVARCHAR(3), 
                    @D28   AS NVARCHAR(3), 
                    @D29   AS NVARCHAR(3), 
                    @D30   AS NVARCHAR(3), 
                    @D31   AS NVARCHAR(3)) 
    RETURNS FLOAT 
    AS 
      BEGIN 
        DECLARE  @PHours  AS FLOAT 
         
        DECLARE  @NumberOfDays  AS INT 
         
        DECLARE  @Day  AS NVARCHAR(4) 
         
        DECLARE  @i  AS INT 
         
        DECLARE  @Date  AS DATETIME 
         
        DECLARE  @StringDate  AS NVARCHAR(20) 
         
        DECLARE  @DayOfTheWeek  AS INT 
         
        DECLARE  @SQL NVARCHAR(2000) 
         
        SET @PHours = 0.0 
         
        SET @i = 1 
         
        IF @Month IN (1,3,5,7, 
                      8,10,12) 
          BEGIN 
            SET @NumberOfDays = 31 
          END 
         
        IF @Month IN (4,6,9,11) 
          BEGIN 
            SET @NumberOfDays = 30 
          END 
         
        IF @Month = 2 
          BEGIN 
            IF @Year IN (2008,2012,2016,2020, 
                         2024) 
              BEGIN 
                SET @NumberOfDays = 29 
              END 
            ELSE 
              BEGIN 
                SET @NumberOfDays = 28 
              END 
          END 
         
        WHILE @i <= @NumberOfDays 
          BEGIN 
            SET @Day = '@D' + Convert(NVARCHAR,@i) 
             
            SET @StringDate = Convert(NVARCHAR,@Year) + '-' + Convert(NVARCHAR,@Month) + '-' + Convert(NVARCHAR,@i) 
             
            SET @Date = Convert(DATETIME,@StringDate,102) 
             
            SET @DayOfTheWeek = Datepart(dw,@Date) 
             
            SET @SQL = ' IF ' + @Day + ' = ''P''  BEGIN IF @DayOfTheWeek = 6 -- On a Friday BEGIN SET @PHours = @PHours + 4.5 END ELSE BEGIN SET @PHours = @PHours + 7.5 END END' 
             
            EXEC Sp_executesql 
              @SQL , 
              N'@D1 nvarchar(3),  @D2 nvarchar(3), @D3 nvarchar(3), @D4 nvarchar(3), @D5 nvarchar(3), @D6 nvarchar(3), @D7 nvarchar(3), @D8 nvarchar(3), @D9 nvarchar(3), @D10 nvarchar(3), @D11 nvarchar(3), @D12 nvarchar(3), @D13 nvarchar(3), @D14 nvarchar(3), @D15 nvarchar(3), @D16 nvarchar(3), @D17 nvarchar(3), @D18 nvarchar(3), @D19 nvarchar(3), @D20 nvarchar(3), @D21 nvarchar(3), @D22 nvarchar(3), @D23 nvarchar(3), @D24 nvarchar(3), @D25 nvarchar(3), @D26 nvarchar(3), @D27 nvarchar(3), @D28 nvarchar(3), @D29 nvarchar(3), @D30 nvarchar(3), @D31 nvarchar(3), @DayOfTheWeek int, @PHours float OUTPUT' , 
              @D1 , 
              @D2 , 
              @D3 , 
              @D4 , 
              @D5 , 
              @D6 , 
              @D7 , 
              @D8 , 
              @D9 , 
              @D10 , 
              @D11 , 
              @D12 , 
              @D13 , 
              @D14 , 
              @D15 , 
              @D16 , 
              @D17 , 
              @D18 , 
              @D19 , 
              @D20 , 
              @D21 , 
              @D22 , 
              @D23 , 
              @D24 , 
              @D25 , 
              @D26 , 
              @D27 , 
              @D28 , 
              @D29 , 
              @D30 , 
              @D31 , 
              @DayOfTheWeek , 
              @PHours OUTPUT 
             
            SET @i = @i + 1 
          END 
         
        -- Return the result of the function 
        RETURN @PHours 
      END 
    
    GO 
    -- CREATE COMPUTED COLUMN USING UDF
    CREATE TABLE Alpha ( 
      Jmonth       INT, 
      JYear        INT, 
      D1           NVARCHAR(3), 
      D2           NVARCHAR(3), 
      D3           NVARCHAR(3), 
      D4           NVARCHAR(3), 
      D5           NVARCHAR(3), 
      D6           NVARCHAR(3), 
      D7           NVARCHAR(3), 
      D8           NVARCHAR(3), 
      D9           NVARCHAR(3), 
      D10          NVARCHAR(3), 
      D11          NVARCHAR(3), 
      D12          NVARCHAR(3), 
      D13          NVARCHAR(3), 
      D14          NVARCHAR(3), 
      D15          NVARCHAR(3), 
      D16          NVARCHAR(3), 
      D17          NVARCHAR(3), 
      D18          NVARCHAR(3), 
      D19          NVARCHAR(3), 
      D20          NVARCHAR(3), 
      D21          NVARCHAR(3), 
      D22          NVARCHAR(3), 
      D23          NVARCHAR(3), 
      D24          NVARCHAR(3), 
      D25          NVARCHAR(3), 
      D26          NVARCHAR(3), 
      D27          NVARCHAR(3), 
      D28          NVARCHAR(3), 
      D29          NVARCHAR(3), 
      D30          NVARCHAR(3), 
      D31          NVARCHAR(3), 
      CompColALPHA AS [dbo].[UDF_P_Hours]([JMonth],[JYear],[D1],[D2],[D3],[D4],[D5],[D6],[D7],[D8],[D9],[D10],[D11],[D12],[D13],[D14],[D15],[D16],[D17],[D18],[D19],[D20],[D21],[D22],[D23],[D24],[D25],[D26],[D27],[D28],[D29],[D30],[D31])); 

    Kalman Toth, SQL Server & BI Training, SSAS OLAP, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Otilia. _ Saturday, February 27, 2010 2:24 PM
    Saturday, February 27, 2010 1:22 PM
    Moderator

  • Thank you very much for your answer.
    It makes sense. I have other computed columns and those work perfect but .... nothing dymanic inside the function.
    Yes, I can write it without the Dynamic SQL but somehow, the code looks stupid. I have to write the same condition for like 30 times which .... I'll do it!
    Thank you very much again! I lost 2 days trying to figure it out.

    Kind regards,
    Otilia
    Saturday, February 27, 2010 2:24 PM