sql server table valued function parameter issue

  • 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

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

  • 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

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

    Incorrect syntax near '('.

    Are you testing on SQL server 2005?

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

  • 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

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

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

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

  • 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

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

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

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