我正在尝试创建一个将用于报表的存储过程,并且我希望两个日期参数的默认值为今天的日期和前一个月的默认值。
下面是做这件事的正确方法吗?我在其他地方读到,我应该使用聚合.(SEE HERE)这是一个敏感的生产系统,所以我想在我前进之前再检查一遍。
CREATE PROCEDURE spCaseNoteReport
-- Add the parameters for the stored procedure here
@ContactStartDate DateTime = null,
@ContactEndDate DateTime = null
AS
IF @ContactStartDate is null
SET @ContactStartDate = dateadd(m,-1, CONVERT(date, GETDATE()))
IF @ContactEndDate is null
SET @ContactEndDate = CONVERT(date, GETDATE())
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT (id.LastName + ', ' + id.FirstName) AS 'MemberName'
,c.ContactDate
,Li.ItemDescription AS 'Location'
,c.PresentAtContact
,c.ContactDetails
,c.InsertUser
,c.TimeSpentUnits
FROM dbo.tblCaseNotes c
inner join dbo.tblIdentificationMap id
on c.PersonID = id.PersonID
left outer join dbo.tblCaseNoteContactLocation L
on c.Casenoteid = L.Casenoteid
inner join dbo.tblCaseNotesMaintItem Li
on L.ContactLocationID = Li.ItemID
WHERE c.ContactDate BETWEEN @ContactStartDate AND @ContactEndDate
ORDER BY MemberName, c.ContactDate, c.InsertUser结束
续
因此,当我试图为此运行CREATE PROCEDURE时,我会得到以下错误->
Msg 243, Level 16, State 1, Procedure spCaseNoteReport, Line 12 Type date is not a defined system type. Msg 243, Level 16, State 1, Procedure spCaseNoteReport, Line 14 Type date is not a defined system type.
发布于 2010-03-01 17:34:06
这种方法没什么不对。我自己用的。
参数缺省值只能是常量或udfs,因此可以选择使用udfs,而这实际上并没有帮助。
编辑:从日期时间中删除时间组件的最佳方法
DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)看看这个很棒的Q+A "Most efficient way in SQL Server to get date from date+time?" (不是我的!)
https://stackoverflow.com/questions/2357692
复制相似问题