SQL Sproc Parameters: Setting Default Date value
I am trying to create a Stored Procedure that will be used for a Report and I want the 2 date parameters to have a DEFAULT value of today's date and 1 month prior.
Is the below the proper way to do this? I was reading elsewhere that I should use COALESCE...(SEE HERE)This is a touchy production system so I wanted to double check before I went forward.
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
END
continu开发者_开发知识库ed
So when I actually tried to run the CREATE PROCEDURE
for this I get the following errors -->
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.
Nothing wrong with this approach. I use it myself.
Parameter defaults can only be constants or udfs so the alternative is to use udfs which honestly doesn't really help.
Edit: best way to remove a time component from datetime
DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
See this excellent SO Q+A "Most efficient way in SQL Server to get date from date+time?" (not mine!)
精彩评论