CODEWITHSUNDEEP
asp.netsql-serversql-server-2008
mazhar 124
mazhar 124

Reputation: 123

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

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.

Upvotes: 0

Views: 252

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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());

Upvotes: 2

anoop
anoop

Reputation: 3822

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

from DATES
option (maxrecursion 0)

Upvotes: 0

Related Questions