sql server table valued function parameter issue

Question

sql server table valued function parameter issue

nzhang6666

SSCrazy

Points: 2217
More actions
September 5, 2012 at 5:43 am

#393103

Hi all,
I got following table valued function (SQL Server 2005). I got an compile error when I run --1, but --3 is ok, --2 is used to generate the parameters to be used in --3, which should be the same as in --1. But why --1 got the error?
create function test_udf_nz_2 (
@a datetime
,@b datetime
)
returns @result TABLE(
c1 datetime
,c2 datetime
)
as
begin
insert into @result
select @a, @b-2
return
end
declare
@dt_report_date DATETIME
,@v_stores VARCHAR(MAX)
select @dt_report_date = '20120831'
,@v_stores = '152'
--1
select * from dbo.test_udf_nz_2( DATEADD(hour,0,DATEDIFF(d,0,@dt_report_date)), DATEADD(hour,24,DATEDIFF(d,0,@dt_report_date))) AS t
--2
--select DATEADD(hour,0,DATEDIFF(d,0,@dt_report_date)), DATEADD(hour,24,DATEDIFF(d,0,@dt_report_date))
--3
select * from dbo.test_udf_nz_2( '20120831', '20120901') AS t

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

except for the missing GO statement between the end of the proc and the DECLARE, when i uncomment #2, all three work perfectly.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Lowell (9/5/2012)
except for the missing GO statement between the end of the proc and the DECLARE, when i uncomment #2, all three work perfectly.

Same here. I wonder what the secret error message was?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nzhang6666 SSCrazy Points: 2217 More actions · Answer 3

I got following error when compiling --1 (commented --2 and --3).

Incorrect syntax near '('.

Are you testing on SQL server 2005?

Lowell SSC Guru Points: 323518 More actions · Answer 4

i just tested it in 2008R2, 2005, and compatibility 80.

the database you are testing in is set for compatibility 80 instead of 90.

in SQL2000 (80), functions were not allowed, and that's why you get the syntax error;

change your database compatibility.

EXEC dbo.sp_dbcmptlevel @dbname=N'SANDBOX', @new_cmptlevel=90

try it in tempdb if you want, and it works fine, since tempdb will be the version of the server.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

nzhang6666 (9/5/2012)
I got following error when compiling --1 (commented --2 and --3).
Incorrect syntax near '('.
Are you testing on SQL server 2005?

It's probably the missing batch separator "GO" after the create function script.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nzhang6666 SSCrazy Points: 2217 More actions · Answer 6

Just found out. The compatible level of the database is set to sql server 2000 (8.0).

I didn't know function can not be used in parameters of a table valued function in 2000...

But I can run a query with recursive CTE on the database, does 2000 supports CTE?

Lowell SSC Guru Points: 323518 More actions · Answer 7

nzhang6666 (9/5/2012)
Just found out. The compatible level of the database is set to sql server 2000 (8.0).
I didn't know function can not be used in parameters of a table valued function in 2000...
But I can run a query with recursive CTE on the database, does 2000 supports CTE?

best way to find out is to try it yourself...but no, CTE's will not work in 2000/ 80 compatibility...you have to change it to subqueries.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nzhang6666 SSCrazy Points: 2217 More actions · Answer 8

Lowell (9/5/2012)
i just tested it in 2008R2, 2005, and compatibility 80.
change your database compatibility.
EXEC dbo.sp_dbcmptlevel @dbname=N'SANDBOX', @new_cmptlevel=90
try it in tempdb if you want, and it works fine, since tempdb will be the version of the server.

You are right. The database is set to be compatible with sql server 2000.

nzhang6666 SSCrazy Points: 2217 More actions · Answer 9

Lowell (9/5/2012)
nzhang6666 (9/5/2012)
Just found out. The compatible level of the database is set to sql server 2000 (8.0).
I didn't know function can not be used in parameters of a table valued function in 2000...
But I can run a query with recursive CTE on the database, does 2000 supports CTE?
best way to find out is to try it yourself...but no, CTE's will not work in 2000/ 80 compatibility...you have to change it to subqueries.

That's what I was thinking, but I just run a CTE, and it worked.

;with cte(a) as (

select 1

union all

select a + 1

from cte

where a < 10

)

select * from cte

nzhang6666 SSCrazy Points: 2217 More actions · Answer 10

Surprisingly, I can also do cross apply...

There might be some difference with running sql server 2005 (set to compatible to sql server 2000 ) and running sql server 2000 directly?