SQL Server table valued function parameter

Question

I got following table valued function (in 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

--1 one works for me (SQL Server 2012) which version are you using? What's the error message? — Andomar, Sep 05 '12 at 11:34
The database is set to be compatible with sql server 8.0 (2000).. Thanks. — thotwielder, Sep 05 '12 at 12:49

score 0 · Answer 1 · answered Sep 05 '12 at 12:47

0

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

answered Sep 05 '12 at 12:47

thotwielder

1,563
7
44
83

SQL Server table valued function parameter

1 Answers1