title: How do you initialize a variable in a stored procedure with a function
body: <p>How do you initialize a variable in a stored procedure with a function?</p>
  
  <p>This doesn't work:</p>
  
  <pre><code>/****** Object:  StoredProcedure [dbo].[usp_ShowBackpopGaps]    Script Date: 05/25/2011 19:57:23 ******/
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  
  -- =============================================
  -- Author:      &lt;Author,,Name&gt;
  -- Create date: &lt;Create Date,,&gt;
  -- Description: &lt;Description,,&gt;
  -- =============================================
  ALTER PROCEDURE [dbo].[usp_ShowBackpopGaps] 
      -- Add the parameters for the stored procedure here
      @StartDate datetime = DateAdd(yy, -1,getdate()), 
      @EndDate datetime = getdate
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      -- Insert statements for procedure here
      ;with dateranges as(
                  select DateAdd(dd,-1,EtlStartDate) as DateFrom,
                  DateAdd(dd,1,EtlEndDate) as DateTo
                  from EtlJobRunStatus
                  where JobRunStepID like 'ETL[0-9]%' and EndTime is not null
                  union all
                  select @StartDate ,@StartDate 
                  union all
                  select DateAdd(dd,-1,@EndDate),DateAdd(dd,-1,@EndDate)    
              )
              select DateAdd(dd,-1,DateTo) as MissingFrom,
                DateAdd(dd,1,NextDateFrom) as MissingTo,
                DateDiff(d,DateTo, NextDateFrom) as MissingDays
              from (
                select distinct DateFrom, DateTo as DateTo, 
                   (select MIN (dateFrom) 
                      from dateranges 
                      where DateTo &gt; D.DateTo
                   ) as NextDateFrom
                from dateranges D
              ) X
              where DateTo &lt; NextDateFrom
  END
  
  
  GO
  </code></pre>