The maximum recursion 100 has been exhausted before statement completion using SQL Function

Question

I have created SQL Function for getting dates into my Gridview, So I am passing start date and end date to my Function but getting error like below.

The maximum recursion 100 has been exhausted before statement completion

If counts date greater than 100 means getting the above error, if below 100 dates means not getting any error. I have records of more than 100 days.

Below is my Data logic.

 public DataTable GetDates(DateTime StartDate, DateTime EndDate)
  {
    DataTable dt = new DataTable();// order by dt desc
    ConnectMethod();
    cmd = new SqlCommand("select dt from dbo.ExplodeDates(@StartDate,@EndDate) order by dt desc", con);
    try
    {
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@StartDate", StartDate);
        cmd.Parameters.AddWithValue("@EndDate", EndDate);
        cmd.ExecuteScalar();
        SqlDataAdapter da = new SqlDataAdapter(cmd);

        da.Fill(dt);
        return dt;
    }
    catch (Exception ex)
    { return dt; }
    finally
    {
        con.Close();
        con.Dispose();
        cmd.Dispose();
    }
}

I am getting this error on cmd.ExecuteScalar(); line. Below is my SQL Function.

  CREATE FUNCTION [dbo].[ExplodeDates](
    @startdate datetime
    , @enddate datetime
   )
  RETURNS TABLE
  AS
  RETURN
    WITH DATES AS(
            SELECT @startdate AS dt
            UNION ALL
            SELECT DATEADD(D, 1, dt) FROM DATES WHERE DT<@enddate
    )
    SELECT * FROM DATES

     GO

Please help to out this Issue looking result since morning.

Just *don't* do that. Create a Calendar table with a large number of years. With only 365 rows per year, it takes no space but improves performance enormously — Panagiotis Kanavos, Apr 04 '17 at 15:22
Already my site is on live please help to how to modify this code only or please update your answer. @Panagiotis Kanavos — mazhar 124, Apr 04 '17 at 15:25
Check Aaron Bertrand's [Creating a date dimension or calendar table in SQL Server](https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/). 50 years of dates take 3MB. You could use the article's technique to avoid recursion, but calculate a date range when you can simply retrieve all the dates you want from a 3MB table? — Panagiotis Kanavos, Apr 04 '17 at 15:26
No I dont want that much of years, only need 200 days for my application, can we do modify for 200 days for on my same above function, please help. @Panagiotis Kanavos — mazhar 124, Apr 04 '17 at 15:28
Read the article, it already contains the scripts you need. Just create a table and *select* only 200 days — Panagiotis Kanavos, Apr 04 '17 at 15:37
Btw: I'd understand to need some kind of *GetDateList()* function in SQL, but don't you think, this is much easier solved with C# directly? — Shnugo, Apr 04 '17 at 15:50

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

You can avoid the recursive CTE with a simple tally-table-on-the-fly:

DECLARE @NumberOfDays INT=200;
DECLARE @StartDate DATE=GETDATE();

WITH Numbers AS
(SELECT TOP (@NumberOfDays) ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 AS Nmbr FROM master..spt_values)
SELECT DATEADD(DAY,Nmbr,@StartDate)
FROM Numbers

master..spt_values should contain enough rows (~2500) to get sure 200 rows back. You can easily enlarge this number by using CROSS JOIN master..spt_values AS v2, which would allow quite huge numbers... (more than 6mio)

UPDATE: a function

   CREATE FUNCTION [dbo].[ExplodeDates]
   (
      @startdate datetime
    , @enddate datetime
   )
  RETURNS TABLE
  AS
  RETURN
    WITH Numbers AS
    (SELECT TOP (DATEDIFF(DAY,@startdate,@enddate)+1) ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 AS Nmbr FROM master..spt_values)
    SELECT DATEADD(DAY,Nmbr,@startdate) AS dt
    FROM Numbers;
  GO

  SELECT * FROM dbo.ExplodeDates({d'2017-03-01'},GETDATE());

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 04 '17 at 15:35

Shnugo

66,100
9
53
114

Why do that each time instead of creating a calendar table once? – Panagiotis Kanavos Apr 04 '17 at 15:36
This I have to add into my function?. @Shnugo – mazhar 124 Apr 04 '17 at 15:37
@PanagiotisKanavos well, you are right and in my projects I do use a calendar table :-D But the OP told explicitly, that no physically stored table should be created - why ever... This approach is (almost) lightning fast... Fast enough for most needs. And btw: the `master..spt_values` is a physically stored table... :-D – Shnugo Apr 04 '17 at 15:38
PanagiotisKanavos I dont want for 50 years so only need dates for 200 days. @Shnugo – mazhar 124 Apr 04 '17 at 15:39
@mazhar124 You replace the code of your function with my code. You can replace `@NumberOfDays` with a simple `DATEDIFF()` with your `@StartDate` and `@EndDate` – Shnugo Apr 04 '17 at 15:40
@mazhar124 Just let the first `SELECT` start with `SELECT TOP (DATEDIFF(DAY,@StartDate,@EndDate)+1) ...` – Shnugo Apr 04 '17 at 15:42
You are not passing EndDate na. @Shnugo – mazhar 124 Apr 04 '17 at 15:42
I am confused, can update all answer please. @Shnugo – mazhar 124 Apr 04 '17 at 15:45
what is this mean: master..spt_values. @Shnugo – mazhar 124 Apr 04 '17 at 15:53
@mazhar124 The master database includes a table with many values, some kind of catalogue (just try `select * from master..spt_values`). I'm not interested in the values, it is just needed as a *filled* table... Using `ROW_NUMBER()` numbers these rows from 1 to n and this running number is the only value needed here... Any table with at least 200 rows could be used here... – Shnugo Apr 04 '17 at 15:56
I got answer because of you and stack over flow is running because of you people, Thank you very much, I have to give gift to you but how it posible thank you very much and you given exact Function code and ITs working to me. thank you once again. @Shnugo – mazhar 124 Apr 04 '17 at 16:00
@mazhar124 I'm glad to read this! Acceptance and (up-)votes are the way to say *Thank you* on StackOverflow. Everything fine here! – Shnugo Apr 04 '17 at 16:02
Why some body down my question and Its very interesting question and typical answer as you given. @Shnugo – mazhar 124 Apr 04 '17 at 16:06
@mazhar124 Never mind. On SO you must get used to down-votes. Voted it up as compensation... Just don't think about it. – Shnugo Apr 04 '17 at 16:08

score 0 · Answer 2 · answered Apr 04 '17 at 15:16

0

You need to add maximum recursion option after your select table. Like :

from DATES
option (maxrecursion 0)

answered Apr 04 '17 at 15:16

anoop

3,812
2
16
28

The maximum recursion 100 has been exhausted before statement completion using SQL Function

2 Answers2

UPDATE: a function