Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sql server table valued function parameter issue Expand / Collapse
Author
Message
Posted Wednesday, September 5, 2012 5:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 9:00 AM
Points: 48, Visits: 235
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
Posted Wednesday, September 5, 2012 5:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
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
Posted Wednesday, September 5, 2012 6:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 7,224, Visits: 13,696
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
Posted Wednesday, September 5, 2012 6:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 9:00 AM
Points: 48, Visits: 235
I got following error when compiling --1 (commented --2 and --3).
Incorrect syntax near '('.

Are you testing on SQL server 2005?
Post #1354470
Posted Wednesday, September 5, 2012 6:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
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
Posted Wednesday, September 5, 2012 6:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 7,224, Visits: 13,696
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1354475
Posted Wednesday, September 5, 2012 6:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 9:00 AM
Points: 48, Visits: 235
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
Posted Wednesday, September 5, 2012 6:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--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
Posted Wednesday, September 5, 2012 6:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 9:00 AM
Points: 48, Visits: 235
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
Posted Wednesday, September 5, 2012 6:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 9:00 AM
Points: 48, Visits: 235
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
Post #1354487
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse