How do you initialize a variable in a stored procedure with a function

How do you initialize a variable in a stored procedure with a function?

This doesn't work:

/****** Object:  StoredProcedure [dbo].[usp_ShowBackpopGaps]    Script Date: 05/25/2011 19:57:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
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 > D.DateTo
                 ) as NextDateFrom
              from dateranges D
            ) X
            where DateTo < NextDateFrom
END


GO

Answers


You can't have a function call as a parameter default.

I think you need

CREATE PROCEDURE [dbo].[usp_ShowBackpopGaps] 
 @StartDate DATETIME = NULL,      
 @EndDate DATETIME = NULL
AS 
BEGIN
SET @StartDate = ISNULL(@StartDate,DATEADD(yy, -1,GETDATE()))
SET @EndDate =  ISNULL(@EndDate, GETDATE())
...

Need Your Help

How can I test JQuery UI Sortable with Cucumber

testing jquery-ui cucumber capybara

I'm tryting to write a cucumber/capybara test to reorder some items and then save them back. Any thoughts on how to best do this?

PHP DRY Throwing InvalidArgumentException

php unit-testing exception exception-handling tdd

In a framework I am building, I am moving towards making my code more testable as I was previously addicted to the MVC+Singleton pattern and had static classes galore. Since then, I have started to

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.