title: Want to display 12 months name from sql server
body: <p>I want to display 12 months name from sql server. i though to accomplish insert month name into temp table and then fire select statement on that table. so i had to write 12 insert table to insert 12 months name. so i search google to find better solution and i got it.</p>
  
  <h2>here is the sql statement</h2>
  
  <pre><code>WITH R(N) AS
  (
      SELECT 0
      UNION ALL
      SELECT N+1 
      FROM R
      WHERE N &lt; 12
  )
  
  SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) AS [month]
  FROM R
  </code></pre>
  
  <p>the above script works perfectly but my problem is i just do not understand how it works. i never work with CTE.</p>
  
  <p>so tell me what is the meaning of  <code>WITH R(N) AS</code></p>
  
  <p>and see this sql</p>
  
  <pre><code>SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) AS [month] FROM R
  when above sql execute how it is getting value for -N ??
  because here i have not set anything for -N ??
  </code></pre>
  
  <p>so please anyone help me to understand how whole thing works. thaks</p>
  
  <h2>My Second Phase of Question</h2>
  
  <p>just have look a and tell me</p>
  
  <pre><code>;WITH months(MonthNumber) AS
  (
      SELECT 0
      UNION ALL
      SELECT MonthNumber+1 
      FROM months
      WHERE MonthNumber &lt; 12
  )
  </code></pre>
  
  <p>onething is not clear to me that why only first time the below part execute</p>
  
  <pre><code>SELECT 0
  UNION ALL
  SELECT MonthNumber+1 
  FROM months
  WHERE MonthNumber &lt; 12
  </code></pre>
  
  <p>and from the 2nd time only this below part execute</p>
  
  <pre><code>SELECT MonthNumber+1 
  FROM months
  WHERE MonthNumber &lt; 12
  </code></pre>
  
  <p>whenever we write two sql statement using Union and execute then always it return data from two sql state but specially in this case from the 2nd time why only this below part
  execute</p>
  
  <pre><code>SELECT MonthNumber+1 
  FROM months
  WHERE MonthNumber &lt; 12
  </code></pre>
  
  <p>basically i not familiar with CTE with recursion technique and that is why things is not getting clear to me. if possible please discuss how CTE recursion works.</p>
  
  <pre><code>DECLARE @TotaDays SMALLINT
  DECLARE @Month VARCHAR(15)
  DECLARE @Year SMALLINT
  DECLARE @date DATETIME 
  SET @Month = 'January'
  SET @Year = 2015
  SET @date = '01 ' + @Month + ' ' + CONVERT(VARCHAR(4),@Year)
  SET @TotaDays = 0
  SELECT @TotaDays = DATEDIFF(DAY, @date, DATEADD(MONTH, 1, @date))
  
  ;WITH months(MonthNumber) AS
  (
      SELECT 1
      UNION ALL
      SELECT MonthNumber+1 
      FROM months
      WHERE MonthNumber &lt; @TotaDays
  )
  
  select * from months;
  </code></pre>