title: SQL Server : how to run a stored procedure at the end of each calendar month
body: <p>I need to execute a stored procedure at the end of each calendar month. it should take end of the current month as finish and end of previous month as start date.</p>
  
  <p>Here examples:</p>
  
  <pre><code>exec my_report @ReportStartDate = '20140731', @ReportEndDate='20140831'
  exec my_report @ReportStartDate = '20140131', @ReportEndDate='20131231'
  exec my_report @ReportStartDate = '20140228', @ReportEndDate='20140131'
  </code></pre>
  
  <p>My aim is to store results in a table. So I need to create a new stored procedure to call the current stored procedure.</p>
  
  <p>I could not find to schedule the <code>my_report</code> stored procedure. So I create a new stored procedure. My aim is to call <code>caller_sp</code> on each day and check the dates inside the caller stored procedure.</p>
  
  <p>Here my caller stored procedure. I have good knowledge of oracle but I an new to SQL Server.</p>
  
  <ol>
  <li>Is there a way to schedule <code>my_report</code> at the end of each calendar month and send begin and end dates.</li>
  <li>Is there a decent version of my code below        </li>
  </ol>
  
  <p>Code:</p>
  
  <pre><code>declare @reportstartyear VARCHAR(4) = null
  declare @ReportEndDate DATETIME = null
  declare @ReportStartDate DATETIME = null
  
  if month(getdate()) = '01' 
  Begin
      if DAY(getdate()) = '31' 
      Begin
          set @reportstartyear = year(getdate())-1
          set @ReportStartDate = cast(@reportstartyear + '1231' as Datetime) 
  
          exec [LTR].[LetterOfGuaranteeProceedsReport] 
               @ReportStartDate, @ReportEndDate = cast(select getdate())
      end
  end
  else if month(getdate())='02' 
  begin
      if year(getdate())%4=0
      begin 
          if day(getdate())='29'
          begin
              set @reportstartyear=year(getdate())
              set @ReportStartDate=cast(@reportstartyear+'0131' as Datetime) 
              exec [LTR].[LetterOfGuaranteeProceedsReport] @ReportStartDate,@ReportEndDate=cast(select getdate())
          end
      end
  end
      else if day(getdate())='28'
      begin
          set @reportstartyear=year(getdate())
          set @ReportStartDate=cast(@reportstartyear+'0131' as Datetime) 
          exec [LTR].[LetterOfGuaranteeProceedsReport] @ReportStartDate,@ReportEndDate=cast(select getdate())
      end
  
  else if month(getdate())='03' 
  begin
      if day(getdate())='31'
      begin 
          if year(getdate())%4=0
          begin
              set @reportstartyear=year(getdate())
              set @ReportStartDate=cast(@reportstartyear+'0229' as Datetime) 
              exec [LTR].[LetterOfGuaranteeProceedsReport] @ReportStartDate,@ReportEndDate=cast(select getdate())
          end
          else 
          begin
              set @reportstartyear=year(getdate())
              set @ReportStartDate=cast(@reportstartyear+'0228' as Datetime) 
              exec [LTR].[LetterOfGuaranteeProceedsReport] @ReportStartDate,@ReportEndDate=cast(select getdate())
          end
  
      end
  end
  </code></pre>