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.
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
|||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,
|||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
No comments:
Post a Comment