sql server table valued function parameter issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » sql server table valued function parameter...

11 posts, Page 1 of 2

1 2 »»

sql server table valued function parameter issue

Rate Topic

Display Mode

Topic Options

Author

Message

nzhang6666

Posted Wednesday, September 05, 2012 5:43 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 2:34 AM
Points: 39, Visits: 184

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
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

Post #1354447

Lowell

Posted Wednesday, September 05, 2012 5:59 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:10 AM
Points: 11,792, Visits: 28,079

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

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1354455

ChrisM@Work

Posted Wednesday, September 05, 2012 6:04 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:29 AM
Points: 5,705, Visits: 11,144

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1354459

nzhang6666

Posted Wednesday, September 05, 2012 6:19 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 2:34 AM
Points: 39, Visits: 184

I got following error when compiling --1 (commented --2 and --3).
Incorrect syntax near '('.

Are you testing on SQL server 2005?

Post #1354470

Lowell

Posted Wednesday, September 05, 2012 6:23 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:10 AM
Points: 11,792, Visits: 28,079

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1354474

ChrisM@Work

Posted Wednesday, September 05, 2012 6:23 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:29 AM
Points: 5,705, Visits: 11,144

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}

Post #1354475

nzhang6666

Posted Wednesday, September 05, 2012 6:26 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 2:34 AM
Points: 39, Visits: 184

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?

Post #1354477

Lowell

Posted Wednesday, September 05, 2012 6:28 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:10 AM
Points: 11,792, Visits: 28,079

nzhang6666 (9/5/2012)

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1354479

nzhang6666

Posted Wednesday, September 05, 2012 6:29 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 2:34 AM
Points: 39, Visits: 184

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.

Post #1354480

nzhang6666

Posted Wednesday, September 05, 2012 6:45 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 2:34 AM
Points: 39, Visits: 184

Lowell (9/5/2012)

nzhang6666 (9/5/2012)

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

Post #1354487

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions