June 29, 2012 at 8:55 am
Hi friends,
Is there a good way to use SYSDATETIMEOFFSET in a default value of a stored procedure ?
Here is my code:
ALTER PROCEDURE [pr_GetLastInterval]
(@i_TimeInterval int = 15,
@dt_End_Block datetime = SYSDATETIMEOFFSET ,
@dt_Start_Output datetime OUTPUT,
@dt_End_Output datetime OUTPUT)
As
SET NOCOUNT OFF
BEGIN
Mycode
END
When I execute this procedure with a value for @dt_End_Block, the procedure give me the expected result:
2007-04-30 12:45:00.000,2007-04-30 13:00:00.000
EXEC pr_GetLastInterval
--@i_TimeInterval =5 ,
@dt_End_Block = "2007-04-30 13:10",
@dt_Start_Output = @pdt_Start OUTPUT, @dt_End_Output = @pdt_End OUTPUT;
SELECT @pdt_Start, @pdt_End
But when I execute this procedure without a value for @dt_End_Block, I expect that the procedure will use the default value SYSDATETIMEOFFSET. Instead I get an error:
Msg 241, Level 16, State 1, Procedure pr_GetLastInterval, Line 0
Conversion failed when converting date and/or time from character string.
EXEC pr_GetLastInterval
--@i_TimeInterval =5 ,
--@dt_End_Block = "2007-04-30 13:10",
@dt_Start_Output = @pdt_Start OUTPUT, @dt_End_Output = @pdt_End OUTPUT;
SELECT @pdt_Start, @pdt_End
Have you any idea ?
Thanks for your help
Julien
NOTE: i've tried with adding paranthesis wich are giving me a bunch of error. : @dt_End_Block datetime = SYSDATETIMEOFFSET()
June 29, 2012 at 9:10 am
Edit: Deleted code, copied wrong stuff.
June 29, 2012 at 9:15 am
Yes , I had seen that my first edit of the post was not the best, so I changed it. Let me know if it's still unclear for you now.
Julien
June 29, 2012 at 9:25 am
It is not allowed you can not use the functions directly as it must be constant. But I think it should throw an error because when you are not passing the parameter as quoted string.
It is treating that value as literal and thus throwing error. It should have thrown error at the time of proc creation because the string is not enclosed in quotes.
To check the behaviour see the below code..
drop proc de
go
create proc de
@dt_End_Block varchar(30) = SYSDATETIMEOFFSET
as
begin
select @dt_End_Block
end
exec de '20120626'
exec de
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
June 29, 2012 at 9:31 am
It is trying to convert the function name to a datetime value. You may need to do something more like this:
ALTER PROCEDURE dbo.pr_GetLastInterval(
@i_TimeInterval int = 15,
@dt_End_Block datetime = null,
@dt_Start_Output datetime OUTPUT,
@dt_End_Output datetime OUTPUT
)
As
BEGIN
SET NOCOUNT OFF
set @dt_Start_Output = dateadd(mi,-1 * @i_TimeInterval, coalesce(@dt_End_Block,sysdatetimeoffset()));
set @dt_End_Output = coalesce(@dt_End_Block,sysdatetimeoffset());
return;
END
go
declare @Date1 datetime, @Date2 datetime;
declare @retval int;
exec @retval = dbo.pr_GetLastInterval @i_TimeInterval = 15, @dt_Start_Output = @Date1 OUTPUT, @dt_End_Output = @Date2 OUTPUT;
select @retval, @Date1, @Date2;
go
June 29, 2012 at 10:13 am
Thank you guys and thank you Lynn for making me discover the coalesce function ! It works perfectly now.
Julien
August 22, 2012 at 12:44 pm
Are you executing from SSMS? If so, make sure you check the "Pass Null Value" checkbox on the dialog box on the datetime column.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply