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

    Question

  • 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
    as
    begin
     
    return exec('SELECT CAST('+@Equation+' AS float)')
    end

    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

    Thanks

     

    Wednesday, August 16, 2006 5:00 PM

Answers

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

     

    Thanks

    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)

    as
    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
    go

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

    ALTER  procedure Proc

    AS

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

    GO

    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