Help Needed - Function Returning Table

Question

Post reply

Help Needed - Function Returning Table

windows_mss

SSCrazy

Points: 2426
More actions
July 24, 2009 at 6:08 am

#208636

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 🙂

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

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

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

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

windows_mss SSCrazy Points: 2426 More actions · Answer 2

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 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 3

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!

windows_mss SSCrazy Points: 2426 More actions · Answer 4

Hey Lowell & Matt u both rocks,

thanks for your guidance

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