Help Needed - Function Returning Table

  • when i compiling the following code, getting this error

    Msg 102, Level 15, State 1, Procedure fnGetNoOfBusinessDays, Line 6

    Incorrect syntax near ';'.

    CREATE FUNCTION dbo.fnGetNoOfBusinessDays

    (@STARTDATE datetime,@EntDt datetime)

    RETURNS TABLE

    AS

    RETURN

    ;with DateList as

    (

    select cast(@STARTDATE as datetime) DateValue

    union all

    select DateValue + 1 from DateList

    where DateValue + 1 < convert(VARCHAR(15),@EntDt,101)
    )select * from DateList where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' )
    GO
    [/code]

    ___
    Known Is An Drop,Unknown Is An Ocean....
    Njoy Programming
    🙂

  • just remove the ;

    CREATE FUNCTION dbo.testFunction

    (@STARTDATE datetime,@EntDt datetime)

    RETURNS TABLE

    AS

    RETURN

    with DateList as

    (

    select cast(@STARTDATE as datetime) DateValue

    union all

    select DateValue + 1 from DateList

    where DateValue + 1 < convert(VARCHAR(15),@EntDt,101)

    )select * from DateList where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' )

    GO

    Let me know if this works

  • hi Matt,

    thanks for your kind reply,

    when i created that function, and executed it like this,

    CREATE FUNCTION dbo.testFunction

    (@STARTDATE datetime,@EntDt datetime)

    RETURNS TABLE

    AS

    RETURN

    with DateList as

    (

    select cast(@STARTDATE as datetime) DateValue

    union all

    select DateValue + 1 from DateList

    where DateValue + 1 < convert(VARCHAR(15),@EntDt,101)

    )select * from DateList where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' )

    GO

    select dbo.testFunction(getdate(),getdate()+30);

    Msg 4121, Level 16, State 1, Line 1

    Cannot find either column "dbo" or the user-defined function or aggregate "dbo.testFunction", or the name is ambiguous.

    guidance needed,

    thanks in advance.

    ___
    Known Is An Drop,Unknown Is An Ocean....
    Njoy Programming
    🙂

  • your function is fine, it's the function call...

    since it is returning a table, you need SELECT * FROM, not SELECT:

    select * from dbo.testFunction(getdate(),getdate()+30);

    --results:

    2009-07-27 00:15:08.460

    2009-07-28 00:15:08.460

    2009-07-29 00:15:08.460

    2009-07-30 00:15:08.460

    2009-07-31 00:15:08.460

    etc....

    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!

  • Hey Lowell & Matt u both rocks,

    thanks for your guidance

    ___
    Known Is An Drop,Unknown Is An Ocean....
    Njoy Programming
    🙂

Viewing 5 posts - 1 through 4 (of 4 total)

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