May 16, 2012 at 9:19 am
Hello,
Executing the following stored procedure give me this error:
"Msg 201, Level 16, State 4, Procedure pr_GetLastInterval, Line 0
Procedure or function 'pr_GetLastInterval' expects parameter '@dt_Start', which was not supplied."
@dt_Start is suppose to be a value returning by the store procedure not an entry, any idea of what is wrong ?
Thanks
--CREATE PROCEDURE [pr_GetLastInterval]
ALTER PROCEDURE [pr_GetLastInterval]
@TimeInterval int,
@dt_Start datetime OUTPUT,
@dt_End datetime OUTPUT
As
SET NOCOUNT OFF --http://msdn.microsoft.com/en-us/library/ms187926.aspx
BEGIN
DECLARE @CurrentMinute int
SET @CurrentMinute = DATEPART(Mi, SYSDATETIMEOFFSET())
IF @CurrentMinute = @CurrentMinute%@TimeInterval
BEGIN
SET @dt_Start= 60-@TimeInterval-(@CurrentMinute-@CurrentMinute%@TimeInterval)
SET @dt_End= @dt_Start + @TimeInterval
END
ELSE
BEGIN
SET @dt_Start= abs(@TimeInterval-(@CurrentMinute-@CurrentMinute%@TimeInterval))
SET @dt_End= @dt_Start + @TimeInterval
END
END
GO
EXEC pr_GetLastInterval @TimeInterval = 15
Go
May 16, 2012 at 9:23 am
julien.dutel (5/16/2012)
Hello,Executing the following stored procedure give me this error:
"Msg 201, Level 16, State 4, Procedure pr_GetLastInterval, Line 0
Procedure or function 'pr_GetLastInterval' expects parameter '@dt_Start', which was not supplied."
@dt_Start is suppose to be a value returning by the store procedure not an entry, any idea of what is wrong ?
Thanks
--CREATE PROCEDURE [pr_GetLastInterval]
ALTER PROCEDURE [pr_GetLastInterval]
@TimeInterval int,
@dt_Start datetime OUTPUT,
@dt_End datetime OUTPUT
As
SET NOCOUNT OFF --http://msdn.microsoft.com/en-us/library/ms187926.aspx
BEGIN
DECLARE @CurrentMinute int
SET @CurrentMinute = DATEPART(Mi, SYSDATETIMEOFFSET())
IF @CurrentMinute = @CurrentMinute%@TimeInterval
BEGIN
SET @dt_Start= 60-@TimeInterval-(@CurrentMinute-@CurrentMinute%@TimeInterval)
SET @dt_End= @dt_Start + @TimeInterval
END
ELSE
BEGIN
SET @dt_Start= abs(@TimeInterval-(@CurrentMinute-@CurrentMinute%@TimeInterval))
SET @dt_End= @dt_Start + @TimeInterval
END
END
GO
EXEC pr_GetLastInterval @TimeInterval = 15
Go
Try this:
DECLARE @pdt_Start DATETIME,
@pdt_End DATETIME;
EXEC pr_GetLastInterval @TimeInterval = 15, @dt_Start = @pdt_Start OUTPUT, @dt_End = @pdt_End OUTPUT;
SELECT @pdt_Start, @pdt_End;
Go
May 16, 2012 at 9:24 am
declare @TimeInterval int=1
declare @dt_Start datetime
declare @dt_End datetime
exec pr_GetLastInterval]
@TimeInterval,
@dt_Start OUTPUT,
@dt_End OUTPUT
select @dt_start,@dt_end
MVDBA
May 16, 2012 at 9:24 am
lynn beat me to it again
MVDBA
May 16, 2012 at 9:30 am
In short - even though they are set up as OUTPUT variables, with no default values they are still required during the procedure call.
If you don't want to pass the variables (i.e. for some reason you don't care to capture the output from the output variables), change your stored procedure declaration slightly:
create PROCEDURE [pr_GetLastInterval]
@TimeInterval int,
@dt_Start datetime =null OUTPUT, --note: the =null supplies a default value to the variable, making it optional
@dt_End datetime =null OUTPUT --note: the =null supplies a default value to the variable, making it optional
as
--etc...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 16, 2012 at 9:33 am
Thanks Mike and thanks Lynn, you are both right. Thus it's the way I was calling the stored procedure that was wrong
Julien
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply