"RETURN statements in scalar valued functions must include an argument" ERROR


  • Hi,

    I am trying to write a function which takes a string as input and returns the computed value.

    I need to use the output of this function as a coulmn in another select query.

    Here is the code (Example: @Equation = '(100*4)+12/272')

    create function dbo.calc(@Equation nvarchar(100))
    returns float
    return exec('SELECT CAST('+@Equation+' AS float)')

    I am getting this error when i compile it

    "RETURN statements in scalar valued functions must include an argument"

    Any suggestions would be appreciated.

    Please respond



    Wednesday, August 16, 2006 5:00 PM


All replies

  • Dynamic SQL is disallowed within TSQL UDFs. So you need to either implement a CLR UDF which doesn't require using dynamic SQL since you can compute the expression using C#/VB.NET code itself. See the thread below also for other ideas.
    Wednesday, August 16, 2006 6:07 PM
  • Hi ,


    Thanks for your reply...Is it possible to write the same functionality in a procedure and call it inside an another procedure...


    I need the output of the first procedure to be a column in another procedure...can you suggest any ways of executing a procedure from another procedure



    Wednesday, August 16, 2006 6:56 PM
  • Yes, you can do it from SPs. You can call another SP by using EXEC. See Books Online for more details.
    Wednesday, August 16, 2006 7:06 PM
  • Thanks for the reply...Here is the sample of the two procedures

    This is the first procedure

    alter procedure calc @Equation nvarchar(100)

    Declare @myResult As float, @Command As nVarchar(500)
    Set @Command = 'Select @outVal = ' + @Equation
    Exec sp_ExecuteSql @Command, N'@outVal float Out', @myResult Out
    select @myResult

    This is the procedure i am calling the first still throws an error... Incorrect syntax near the keyword 'exec'

    ALTER  procedure Proc


    select distinct COl1 as Fab,exec calc  Equation as COL2 from Table1


    Please get back

    Thank you

    Wednesday, August 16, 2006 7:26 PM
  • You cannot call a SP or use EXEC statement in a SELECT statement like that. Check Books Online for syntax on SELECT and EXEC statement. There are lot of examples that show how to use those and various restrictions on EXEC statement. If you want to replace UDF in SELECT then you have to either write a TSQL UDF like shown in my first post or use CLR UDF for example.
    Wednesday, August 16, 2006 9:16 PM