如何用函数初始化存储过程中的变量 [英] 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?
这不起作用:
/****** 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
推荐答案
您不能将函数调用作为参数默认值.
You can't have a function call as a parameter default.
我认为你需要
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())
...
这篇关于如何用函数初始化存储过程中的变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文