Multi-statement table-valued UDF problem

Question

Multi-statement table-valued UDF problem

Johnny Doyle

Right there with Babe

Points: 745
More actions
January 2, 2004 at 3:29 am

#161284

I have a multi-statement table-valued UDF which takes a datetime parameter. The following example is a simplified version to recreate the problem I'm encountering (I know this example acheives what could be done with an inline table-valued function or a simple query. My real UDF does do a bit more.):
CREATE FUNCTION dbo.udf_Test (@dtTestDate datetime)
RETURNS @retTestTable TABLE (id int)
AS
BEGIN
INSERT @retTestTable
SELECT id
FROM myTable
WHERE dateCol = @dtTestDate
RETURN
END
This works fine if I invoke it with a hard-coded date. Eg.
SELECT *
FROM myTable
WHERE id IN (SELECT id FROM udf_Test('01/01/2004'))
But I get a syntax error when invoking it with the GetDate() function as the parameter. Eg.
SELECT *
FROM myTable
WHERE id IN (SELECT id FROM udf_Test(getDate()))
I've used getDate() as the parameter with other UDFs without problem, but those UDFs were scalar valued functions.
Is it possible to pass functions like getDate() as the parameters to multi-statement table-valued UDFs?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sachin Dedhia SSCrazy Points: 2104 More actions · Answer 1



SELECT * 

FROM myTable

WHERE id IN (SELECT udf_Test(getDate()))

HTH

Sachin

Happy 24

Regards,
Sachin Dedhia

kcdunn SSC Veteran Points: 281 More actions · Answer 2

I don't think you can use the getdate function like that it needs to be in a SELECT or WHERE clause. You can call your funtion like this

DECLARE @curr_date datetime

SELECT @curr_date = getdate()

SELECT *

FROM myTable

WHERE id IN (SELECT id FROM udf_Test(@curr_date))

Johnny Doyle Right there with Babe Points: 745 More actions · Answer 3

Thanks for the prompt replies.

Sachin, I tried your suggestion but got the following error:

'udf_Test' is not a recognized function name

kcdunn, I wanted to use the UDF in a view definition, so assigning the getdate() value to a variable and passing this variable to the UDF doesn't really help me. Thanks anyway.

Sachin Dedhia SSCrazy Points: 2104 More actions · Answer 4

oops...missed one thing...



SELECT * FROM myTableWHERE id IN (SELECT DBO.udf_Test(getDate()))

Sachin

Regards,
Sachin Dedhia

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

AFAIK it is not possible to directly pass GETDATE() to a UDF (sorry!)

This might be a workaround



CREATE VIEW GetNow

AS 

SELECT GETDATE() AS Now

GO



CREATE function sampledate ()

RETURNS DATETIME

AS

BEGIN

RETURN(SELECT now FROM getNow)

END

GO

SELECT dbo.sampledate()

GO

DROP VIEW GetNow

Go

DROP FUNCTION sampledate

GO

                                                      

------------------------------------------------------ 

2004-01-02 15:13:05.793



(1 row(s) affected)

Frank

http://www.insidesql.de

http://www.familienzirkus.de

Edited by - Frank Kalis on 01/02/2004 07:17:54 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Reading the thread and my post I think you should foret it!

Sorry if it has lead to confusion. Should teach not to concentrate on other things while posting.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Johnny Doyle Right there with Babe Points: 745 More actions · Answer 7

Sachin, I tried you suggestion (including the 'dbo' prefix on the UDF name) and still got an error as follows:

Invalid object name 'dbo.udf_Test'

Sachin Dedhia SSCrazy Points: 2104 More actions · Answer 8

quote:
AFAIK it is not possible to directly pass GETDATE() to a UDF (sorry!)

Do try this piece of code Frank



CREATE FUNCTION dbo.fntest(@dt as datetime)

RETURNS int

AS

BEGIN

RETURN (1)

END

GO

SELECT dbo.fntest(getdate())

GO

DROP FUNCTION dbo.fntest

GO

Problem is when the RETURN type is TABLE.

Reading the thread and my post I think you should forget it!

Sorry if it has lead to confusion. Should teach not to concentrate on other things while posting.

[/guote]

I will stick to you advice Frank

Sorry JD, hard luck .

Frank has the right solution.

Sachin

Better luck next time...

Regards,
Sachin Dedhia

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Äh, now I'm really confused! I still believe my post was not targeted to the original question.

Did we find a solution or not?

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Johnny Doyle Right there with Babe Points: 745 More actions · Answer 10

Still no solution Frank, but thanks for you comments.

I've come across another problem (ADO issue - so I guess its not for this forum) which means I'm going to have to rethink things anyway. Game over.