locked
calling function in select statement in sql RRS feed

  • Question

  • User-952877843 posted

    i want to create this query as function and then calling in in another query inside select statement

    SELECT sum(Sales_Invoice.grand_total) FROM Sales_Invoice

    Monday, August 27, 2018 7:38 AM

Answers

  • User-369506445 posted

    hi

    you can try below

    CREATE FUNCTION dbo.CalcSum (@input VARCHAR(50))
    RETURNS VARCHAR(50)
    AS BEGIN
        DECLARE @result VARCHAR(50)
    
    	SELECT @result=sum(Sales_Invoice.grand_total) FROM Sales_Invoice
    
        RETURN @result
    END

    and use

    select dbo.CalcSum(Sales_Invoice.grand_total) from Sales_Invoice

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 27, 2018 7:48 AM
  • User-369506445 posted

    First, make sure you did run the create script in the correct database.

    Second, as @input VARCHAR(50) started mentioning, you are using the function results incorrectly.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 27, 2018 8:46 AM

All replies

  • User-369506445 posted

    hi

    you can try below

    CREATE FUNCTION dbo.CalcSum (@input VARCHAR(50))
    RETURNS VARCHAR(50)
    AS BEGIN
        DECLARE @result VARCHAR(50)
    
    	SELECT @result=sum(Sales_Invoice.grand_total) FROM Sales_Invoice
    
        RETURN @result
    END

    and use

    select dbo.CalcSum(Sales_Invoice.grand_total) from Sales_Invoice

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 27, 2018 7:48 AM
  • User-952877843 posted

    i have this error

    Cannot find either column "dbo" or the user-defined function or aggregate "dbo.CalcSum", or the name is ambiguous. 

    Monday, August 27, 2018 8:05 AM
  • User-369506445 posted

    First, make sure you did run the create script in the correct database.

    Second, as @input VARCHAR(50) started mentioning, you are using the function results incorrectly.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 27, 2018 8:46 AM