MAXRECURSION in View ??? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

MAXRECURSION in View ???

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2010-02-02 : 20:35:16

Can the MAXRECURSION be used in a View?

The following CTE runs fine, but when I try to Create a View using it, it says syntax error near word 'option'

USE [Shale_Ticket]
GO
/****** Object: View [dbo].[VW_Shale_EOGDiscLeaseDt] Script Date: 05/18/2009 13:28:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[VW_Shale_EOGDiscLeaseDt]
AS

With Date_CTE (Date) AS
(
SELECT DATEADD(DAY,DATEDIFF(DAY,0,'9/12/2009'),0)

UNION ALL

SELECT DATEADD(d,1,Date)
FROM Date_CTE
WHERE DATEADD(d,1,Date)<DATEADD(DAY,DATEDIFF(DAY,0,GETDATE())+1,0)
)

SELECT Date FROM Date_CTE
OPTION (MAXRECURSION 32000)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-03 : 01:20:15

Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2009/07/11/cte-in-a-view.aspx

Madhivanan

Failing to plan is Planning to fail

osupratt
Posting Yak Master

238 Posts

Posted - 2010-02-03 : 10:20:09

this is what i revised to, but when selecting the view and 'running' the view i get the "The statement terminated. The maximum recursion 100 has been exhausted before statement completion." error.

USE [Shale_Ticket]
GO
/****** Object: View [dbo].[VW_SHALE_EOG_DiscDate] Script Date: 05/18/2009 13:28:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[VW_SHALE_EOG_DiscDate]
AS

With [VW_SHALE_EOG_DiscDate](Date) AS
(
SELECT DATEADD(DAY,DATEDIFF(DAY,0,'9/12/2009'),0)

UNION ALL

SELECT DATEADD(d,1,Date)
FROM [VW_SHALE_EOG_DiscDate]
WHERE DATEADD(d,1,Date)<DATEADD(DAY,DATEDIFF(DAY,0,GETDATE())+1,0)
)

SELECT Date FROM [VW_SHALE_EOG_DiscDate]

GO
SELECT * FROM [VW_SHALE_EOG_DiscDate] OPTION (MAXRECURSION 32000)

is there a way to build this query (all dates from 9/1/2010 to GETDATE()) other than with a CTE? thanks for your help.

osupratt
Posting Yak Master

238 Posts

Posted - 2010-02-03 : 10:46:49

Nevermind, something as simple as this did the trick:

USE [Shale_Ticket]
GO
/****** Object: View [dbo].[VW_SHALE_EOG_DiscDate] Script Date: 05/18/2009 13:28:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[VW_SHALE_EOG_DiscDate]
AS

SELECT DATEADD(d,number,'9/11/2009')AS Date

FROM master..spt_values

WHERE number BETWEEN 1 AND

DATEDIFF(d,'9/11/2009',GETDATE())

AND type = 'P'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources