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

Help Needed - Function Returning Table Expand / Collapse
Author
Message
Posted Friday, July 24, 2009 6:08 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 2:43 AM
Points: 14, Visits: 80
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



Known Is A Drop,UnKnown Is An Ocean....
Njoy Programming
:)
Post #758971
Posted Friday, July 24, 2009 6:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:16 PM
Points: 1,207, Visits: 1,249
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
Post #758982
Posted Sunday, July 26, 2009 9:54 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 2:43 AM
Points: 14, Visits: 80
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 A Drop,UnKnown Is An Ocean....
Njoy Programming
:)
Post #759874
Posted Sunday, July 26, 2009 10:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:34 PM
Points: 12,755, Visits: 31,122
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

--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 #759878
Posted Sunday, July 26, 2009 10:35 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 2:43 AM
Points: 14, Visits: 80
Hey Lowell & Matt u both rocks,

thanks for your guidance


Known Is A Drop,UnKnown Is An Ocean....
Njoy Programming
:)
Post #759881
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse