none
Calling a stored procedure or function from another stored procedure

    Question

  • Hello people,

    When I am trying to call a function I made from a stored procedure of my creation as well I am getting:

    Running [dbo].[DeleteSetByTime].

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

    No rows affected.

    (0 row(s) returned)

    @RETURN_VALUE =

    Finished running [dbo].[DeleteSetByTime].

    This is my function:

    ALTER FUNCTION dbo.TTLValue

    (

    )

    RETURNS TABLE

    AS

    RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'

    This is my stored procedure:

    ALTER PROCEDURE dbo.DeleteSetByTime

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @TTL int

    SET @TTL = dbo.TTLValue()

    DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime)

    END

    CreatedTime is a datetime column and TTL is an integer column.

    I tried calling it by dbo.TTLValue(), dbo.MyDatabase.TTLValue(), [dbo].[MyDatabase].[TTLValue]() and TTLValue(). The last returned an error when saving it "'TTLValue' is not a recognized built-in function name". Can anybody tell me how to call this function from my stored procedure? Also, if anybody knows of a good book or site with tutorials on how to become a pro in T-SQL I will appreciate it.

    Your help is much appreciated.

    Friday, March 02, 2007 4:09 PM

Answers

All replies

  • Hi,

    you function returns a table, therefore is it not recognized as a hit for the SET operation, use RETURNS INT instead and return the Value using RETURN(Select the Value from the query)

    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    Friday, March 02, 2007 4:45 PM
  • Hi Jens,

    Would you mind giving an example of that? I tried this:

    ALTER FUNCTION dbo.TTLValue

    (

    )

    RETURNS INT

    AS

    RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'

    ... and received "Incorrect syntax near 'RETURN'", even with parenthesis around the SELECT statement. Thanks for your help!

    Cheers,

    Friday, March 02, 2007 5:31 PM
  • CREATE FUNCTION dbo.TTLValue ()

    RETURNS INT

    AS

    BEGIN

    RETURN(SELECT Settings.TTL FROM Settings WHERE Enabled='true')

    END


    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    Sunday, March 04, 2007 5:15 PM
  • Ha! So Begin and End... That sure makes me look like a fool!
    Wednesday, March 07, 2007 8:40 PM
  • @Jens

    Hey man, thank you for the code i was going crazy trying to wrap a stored procedure returning a result with a function to return the stored procedure result (VERY MESSY). What I was forgetting in my query was the BEGIN and the END tag, how dumb. 

     

    Again, thanks for posting this result.

    Rey

    Miami Web Design

    Tuesday, October 19, 2010 2:44 PM