Multi-statement Table Function with dateTime param

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Multi-statement Table Function with dateTime param

Author

Topic

DDaku
Starting Member

6 Posts

Posted - 2010-01-18 : 11:10:06

First, my function:


CREATE FUNCTION test_fnTVF01 (@inDate dateTime)
RETURNS @resultSet TABLE (id int primary key identity(0,1), descriptor varchar(80))
AS
BEGIN
  INSERT INTO @resultSet (descriptor)
      SELECT [description] FROM tblContracts WHERE (effectiveDate <= @inDate) AND ((expirationDate IS NULL) OR (expirationDate > @inDate))
			
  RETURN
END

Which is just a simple UDF which returns a table (the actual function I'm working on is a bit more complex and uses some actual T-SQL, necessitating the Multi-Statement approach to a Table-valued Function) and uses a dateTime parameter.

To test this I've run the following 4 statements/blocks:


SELECT * FROM test_fnTVF01('2010-01-18')	-- Works: 4205 rows

DECLARE @dt dateTime
SET @dt = current_timestamp
SELECT * FROM test_fnTVF01(@dt)	-- Works: 4205 rows

SELECT * FROM test_fnTVF01(current_timestamp)	-- Fails: Incorrect syntax near the keyword 'current_timestamp'.
SELECT * FROM test_fnTVF01(getDate())	-- Fails: Line 1: Incorrect syntax near '('.

The first 2 calls work fine, when I am providing the dateTime parameter's value as a literal string or as a T-SQL variable. However, when I attempt to call the function with current_timestamp or getDate() (or a sub-query to select the top 1 row from a table containing dates, I was getting desperate) the call fails with a syntax error.

I'm trying to use this function in a view (JOIN'd to a table), so I can't use a variable. And I can't have the date hard-coded, either.

Anyone have an idea what's going on here? I know I can't use a non-deterministic value like current_timestamp within a function, which is why I created a dateTime parameter.

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 11:36:30

Works fine (for me) in SQL2008.

Fails (for me) in SQL2000.

Dunno why ... can you work around it (e.g. using an @variable) ?

I certainly pass in GetDate() into UDFs, to work around them not allow non-deterministic values within the function, but we have a working variable, @dtNow, in all our SProcs, so I expect I am passing that, rather than GetDate()

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 11:39:06

Works on SQL2005 also.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-18 : 12:49:06

you can't pass a function to a function -- which is what you're doing with current_timestamp/getdate

as Kristen suggested, use a variable


Declare @dt smalldatetime
Set @dt = CURRENT_TIMESTAMP

SELECT * FROM test_fnTVF01(@dt)

DDaku
Starting Member

6 Posts

Posted - 2010-01-18 : 12:54:07

I'm stuck with using MS SQL2000 and using a view to call this function, so I can't use a @variable in the view.

One work-around I've found is to remove the parameter from the function and add:


DECLARE @inDate dateTime
  SET @inDate = (SELECT dt FROM OPENQUERY(aLinkedServerNameHere, 'SELECT current_timestamp as dt'))

Which makes the function non-deterministic, but still works. Testing now to see how much of a performance hit this causes. Hopefully it will be slight enough to work with.

I'll also try it out in SQL2005, just for curiosity... (Don't have an SQL2008 server available)

DDaku
Starting Member

6 Posts

Posted - 2010-01-18 : 13:06:02

Russel, is this only for multi-statement table functions? I have plenty of other UDFs which I pass current_timestamp to, to avoid the non-deterministic limitation.

My example works fine for me in SQL2005. Unfortunately this project needs to work on an SQL2000 server.

And to elaborate on the reason I can't use a variable, the context is something like:


CREATE VIEW theGoal AS
SELECT a.firstField, a.secondField, b.firstField
FROM a
LEFT JOIN fnMultiStatementTableFunc(current_timestamp) as b ON (a.foreignKey = b.key)

So, no T-SQL.

Good news is that using OPENQUERY within the function hasn't degraded the performance too much, so I should be able to use that.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-18 : 15:21:21

What if you abandon the function and define the view as


CREATE VIEW theGoal AS
SELECT	a.firstField, a.secondField, b.firstField
FROM	a
LEFT JOIN (
	SELECT	[description]
	FROM	tblContracts
	WHERE	effectiveDate <= current_timestamp
	AND	(
		(expirationDate IS NULL)
		OR
		(expirationDate > current_timestamp)
	)
) b
On	a.foreignKey = b.key

DDaku
Starting Member

6 Posts

Posted - 2010-01-18 : 15:31:02

quote:
Originally posted by russell

What if you abandon the function and define the view as...

From my original post:

quote:
(the actual function I'm working on is a bit more complex and uses some actual T-SQL, necessitating the Multi-Statement approach to a Table-valued Function)

My actual function is a bit involved, using 2 memory tables to build the final returned table.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-18 : 16:46:55

let's see the actual function. perhaps you could use the approach above, but use #temp tables

DDaku
Starting Member

6 Posts

Posted - 2010-01-19 : 08:45:40

Russel, no offense, but you've been wrong once in this thread and ignored what I said twice.

The function is about 90 lines long and deals with some specific business logic that I neither care to, nor feel authorized to, explain.

I appreciate the help and your replies, but the problem is simply this: In a view, pass the current date to a multi-statement table-value function. The contents of the function are irrelevant.

Based on my experiences and the comments here (thanks Kristen!), I'm assuming this is a limitation/bug of SQL2000. I have also found an acceptable, if not ideal, workaround by using OPENQUERY in my function to select the current_timestamp value from another server.

If you, or anyone else, can shed any light on the issue of calling a function like the "test_fnTVF01" example I posted with current_timestamp instead of '2010-01-19' or @aDateVariable, please, please do.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-19 : 10:56:37

I haven't been wrong nor ignored what you said.

Good luck with it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources