locked
Using SQL UDF for a computed column RRS feed

  • Question

  • User2054207217 posted

    Hi:

    I am able to use this stmt. for getting the results I want:

    Select SUM(TodaysLeads) OVER(PARTITION BY Username, MONTH(DateChosen)) 
    from tblA

    I am trying to put this in a function so that I can add the function as a computed column in the table. But the computed column is always returning zero.

    Function:

    FUNCTION dbo.CalculatedMTDLeads
    (
       
    )
    Returns INTEGER
    AS
    BEGIN
        DECLARE @MTDLeads AS INT
    	SELECT  
    	  @MTDLeads =SUM(TodaysLeads) OVER(PARTITION BY Username, MONTH(DateChosen)) 
         
      FROM tblA
      RETURN	@MTDLeads;
    END

    I added a new column to the table tblA as this function, but getting zero values for every row. As I indicated earlier, my first sql statement running on the table itself yields correct results.

    Any thoughts?

    Thanks.

    Thursday, May 21, 2020 10:06 PM

Answers

  • User1686398519 posted

    Hi,  progdever

    First of all, if you use the method I mentioned above, because "CalculatedMTDLeadsNew" returns a table, you will get an error if you write like this.

    Note: For more usage of ALTER, please refer to this link.

    Second, I guess you want to store the value returned by Function in a the computed column of the database. You can create a computed column first, and then store the value in the calculated column.

    CREATE FUNCTION dbo.CalculatedMTDLeads39
    (   
    )
    Returns TABLE
    AS
    RETURN	
        SELECT Id,SUM(TodaysLeads) OVER(PARTITION BY Username, MONTH(DateChosen)) AS MTDLeads
        FROM tblA
    ALTER TABLE dbo.tblA
    ADD MTDLeadsNew INT
    Update A  
    Set A.MTDLeadsNew=B.MTDLeads
    FROM tblA A ,dbo.CalculatedMTDLeads39() B
    where A.Id=B.Id

    Here is the result.

     
    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 25, 2020 7:43 AM

All replies

  • User1686398519 posted

    Hi,  progdever

    Your code can only return the last value, I guess the last value of your data is zero, so each of your rows is zero.

    You should use Table-Valued User-Defined Functions.

    Note:For more information about "Use Table-Valued User-Defined Functions", please check this link.

    CREATE FUNCTION dbo.CalculatedMTDLeads5
    (   
    )
    Returns TABLE
    AS
    RETURN	
        SELECT SUM(TodaysLeads) OVER(PARTITION BY Username, MONTH(DateChosen)) AS MTDLeads
        FROM tblA
    
    SELECT MTDLeads FROM dbo.CalculatedMTDLeads5()

    Here is the result.

    Best Regards,

    YihuiSun
     

    Friday, May 22, 2020 11:53 AM
  • User2054207217 posted

    Hi YihuiSun,

    Thanks, but, when I add this for a computed column, I get an error:

    ALTER TABLE dbo.tblA
     ADD MTDLeadsNew AS dbo.CalculatedMTDLeadsNew()

    Error: Msg 4121, Level 16, State 1, Line 1
    Cannot find either column "dbo" or the user-defined function or aggregate "dbo.CalculatedMTDLeadsNew", or the name is ambiguous.

    Friday, May 22, 2020 4:25 PM
  • User1686398519 posted

    Hi,  progdever

    First of all, if you use the method I mentioned above, because "CalculatedMTDLeadsNew" returns a table, you will get an error if you write like this.

    Note: For more usage of ALTER, please refer to this link.

    Second, I guess you want to store the value returned by Function in a the computed column of the database. You can create a computed column first, and then store the value in the calculated column.

    CREATE FUNCTION dbo.CalculatedMTDLeads39
    (   
    )
    Returns TABLE
    AS
    RETURN	
        SELECT Id,SUM(TodaysLeads) OVER(PARTITION BY Username, MONTH(DateChosen)) AS MTDLeads
        FROM tblA
    ALTER TABLE dbo.tblA
    ADD MTDLeadsNew INT
    Update A  
    Set A.MTDLeadsNew=B.MTDLeads
    FROM tblA A ,dbo.CalculatedMTDLeads39() B
    where A.Id=B.Id

    Here is the result.

     
    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 25, 2020 7:43 AM
  • User2054207217 posted

    Hi YihuiSun,

    Thanks for this. So this would mean I have to run the update statement constantly to get the calculated column value? I was hoping that this gets calculated automatically. Any thoughts?

    Thanks. 

    Tuesday, May 26, 2020 5:05 PM
  • User1686398519 posted

    Hi,  progdever

    As long as you call dbo.CalculatedMTDLeads39 (), it will be calculated. "Update" is used to add the result returned by the function to calculated column.

    Remarks: These links can help you, please refer to it.

    1. Scalar Functions:https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-scalar-functions/
    2. Table Variables:https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-variables/
    3. Computed Columns In SQL Server:https://www.c-sharpcorner.com/article/computed-columns-in-sql-server/

    Best Regards,

    YihuiSun

    Wednesday, May 27, 2020 7:11 AM