August 19, 2009 at 8:06 am
Hi All,
Hoping someone can help with my query.
I have a database that tracks printing data for various users.
We have managed to create a stored procedure to generate data for the required reports. When we execute the SP we need to pass along the parameters for the @StartDate and @EndDate variables in the procedure.
I am trying to set the EndDate variable to the previous day's date. And the StartDate variable should be on month before. When I altered the stored proc to SET the Date values, it creates/alters the procedure successfully.
However, when I execute the procedure I get the following error:
Msg 201, Level 16, State 4, Procedure xgs_DeparmentSummaryUsersTop10Sched, Line 0
Procedure or function 'xgs_DeparmentSummaryUsersTop10Sched' expects parameter '@StartDate', which was not supplied.
The code from the Stored Procedure to declare the variables and set their values is as follows:
@StartDate datetime,
@EndDate datetime
as
set @EndDate = dateadd(s,-1,dateadd(dd, datediff(d,0,getdate()),0))
set @StartDate = dateadd(mm,-1,@EndDate)
Any ideas as to what I am doing wrong?
August 19, 2009 at 8:39 am
Set another @datetime variable in your calling code to be the date you want and pass that to the sproc instead of anything with getdate() in it.
You could also set the default value of the enddate in the stored proc to be what you think it shoudl be.
~BOT
Craig Outcalt
August 24, 2009 at 8:32 am
Thanks Bot,
I have taken what you said into consideration. Unfortunately I cannot set the end date in the stored procedure as it will change every month. I cannot afford to have to change the SP every month.
I am trying to automate the running of the SP in order to generate reports.
I tried some different dateadd calculations, and have come up with the following two:
select dateadd(s,-1,dateadd(dd, datediff(d,0,getdate()),0))
select dateadd(m,-1, dateadd(s,-1,dateadd(dd, datediff(d,0,getdate()),0))
The first line correctly returns the EndDate, which should be the previous day's date. The second line correctly returns the StartDate which should be one month prior to EndDate.
When I run those two lines, I get the correct values. When I try that in conjunction to executing the SP, like so:
exec xgs_DeparmentSummaryUsersTop10 @StartDate = dateadd(m,-1, dateadd(s,-1,dateadd(dd, datediff(d,0,getdate()),0)) ), @EndDate = dateadd(s,-1,dateadd(dd, datediff(d,0,getdate()),0))
I get the following error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'm'.
No matter what I try, it does not want to work.
August 25, 2009 at 8:58 am
You can't pass a value to a stored proc like that.
You must do it like this:
DECLARE @start DATETIME, @end DATETIME
SELECT @start = dateadd(m,-1, dateadd(s,-1,dateadd(dd, datediff(d,0,getdate()),0)) ),
@end = dateadd(s,-1,dateadd(dd, datediff(d,0,getdate()),0))
exec xgs_DeparmentSummaryUsersTop10
@StartDate = @start,
@EndDate = @end
A previous poster had already suggested this.
August 25, 2009 at 9:25 am
I'm curious why you are passing dates at all since it seems you know what values you want on any given day. Put the calculation logic into the sproc and call it without parameters. OR if you want you can keep them and only pass them when you want something different..
CREATE PROCEDURE ReportSprocName
@StartDt datetime = NULL,
@EndDt datetime = NULL
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
-- Set default if one isn't passed
IF ( @EndDt IS NULL )
BEGIN
SELECT @EndDt = DATEADD( dd, -1, GETDATE() )
END
-- Condition End Date so we only have a date
SELECT @EndDt = CONVERT( varchar(10), @EndDt, 101 )
-- Set default if one isn't passed
IF ( @StartDt IS NULL )
BEGIN
SELECT @StartDt = DATEADD( mm, -1, @EndDt )
END
-- Condition Start Date so we only have a date
SELECT @StartDt = CONVERT( varchar(10), @StartDt, 101 )
-- ------------------
-- ------------------
-- Your logic here..
-- ------------------
-- ------------------
END
GO
CEWII
August 25, 2009 at 9:45 am
Good point Elliot,
Just remove the parameters completely if you always set them in the proc anyway.
CREATE PROCEDURE yourProcNameHere
AS
BEGIN
DECLARE @EndDate DATETIME,
@StartDate DATETIME
SELECT @EndDate = dateadd(ms,-3,dateadd(dd, datediff(d,0,getdate()),0)),
@StartDate = dateadd(mm,-1,@EndDate)
-- ------------------
-- ------------------
-- Your logic here..
-- ------------------
-- ------------------
END
GO
August 25, 2009 at 9:53 am
Nigel,
I was trying to figure out what you were trying to accomplish with your end date formula, I think I finally understand. I hate dealing with time when it is not necessary. That is why I filtered time off the values in my code. As a point time often gets forgotten when dealing with datetime data types. As a secondary point I explicitly do not deal with times if they are not required.
CEWII
August 25, 2009 at 10:01 am
Elliot,
The original poster subtracted a whole second from midnight giving a time of 23:59:59.000
which if used in a where clause has the potential to miss rows with a time component between 23:59:59.003 and 23:59:59.997 to avoid this you need to subtract 3 milliseconds instead.
Just something to be aware of.
Nigel
August 25, 2009 at 10:13 am
Oh, I fully agree, I try to take seconds(or fractions) out of the equation, but novice developers often (usually?) forget these ramifications. As a matter of fact I'd rather take the end date to midnight this morning:
SELECT @EndDt = CONVERT( varchar(10), GETDATE(), 101 )
And use a < that will absolutely capture everything yesterday. Given that you and I know the resolution of datetime is ~3ms taking 3ms off the time is pretty much a guarantee, but I hate dealing with time altogether. I'm not sure if there is a performance difference to my way of thinking. If the field is indexed (and in this case I hope it is) then either your way or mine should perform about the same. But it is too easy to forget about the time portion, even for an experienced develop so if I can avoid it altogether I will, every time.
CEWII
August 25, 2009 at 11:44 pm
Thanks all for your suggestions.
The code Mr & Mrs 500 suggested have already been tried. I don't get any errors, but I also don't get any data.
SSChasing Mays: I am trying to use these kind of parameters as at the end of any month I have a lot of reports to generate and we want to ideally have them automated. That way we will (hopefully) not need human input.
The reason why I am subtracting 1 second from the current date is that I borrowed that code from another procedure. I do not specifically need times, just date.
I will try all the suggestions today.
Thank you all for your input.
August 26, 2009 at 12:21 am
Okay, firstly I'm an idiot. Was looking at the wrong places for your names. You can feel free to mock me mercilessly. Thanks, Elliot & Nigel. 🙂
I took Nigel's second suggestion, but changed the code like so:
CREATE PROCEDURE yourProcNameHere
AS
BEGIN
DECLARE @EndDate DATETIME,
@StartDate DATETIME
SELECT @EndDate = dateadd(d,-1,dateadd(dd, datediff(d,0,getdate()),0)),
@StartDate = dateadd(mm,-1,@EndDate)
-- ------------------
-- ------------------
-- Your logic here..
-- ------------------
-- ------------------
END
GO
Changed it from subtracting 3 ms from the current date, to subtracting 1 day from the current date.
That gives me the same data I get when I run the stored proc manually.
Thanks again to you all for your assistance.
August 26, 2009 at 12:35 am
Nigel & Elliot (I got your names right this time), I tried Nigel's second suggested bit of code.
Except that I changed it from subtracting 3 milliseconds, to subtracting 1 day from the current date.
Also changed the query in the stored proc to look for data BETWEEN those dates, not greater than & less than. If that makes sense at all.
Thanks to you both for your help.
August 26, 2009 at 12:39 am
Nigel & Elliot (I got your names right this time), I tried Nigel's second suggested bit of code.
Except that I changed it from subtracting 3 milliseconds, to subtracting 1 day from the current date.
Also changed the query in the stored proc to look for data BETWEEN those dates, not greater than & less than. If that makes sense at all.
Thanks to you both for your help.
August 26, 2009 at 1:47 am
Nigel & Elliot (I got your names right this time), I tried Nigel's second suggested bit of code.
Except that I changed it from subtracting 3 milliseconds, to subtracting 1 day from the current date.
Also changed the query in the stored proc to look for data BETWEEN those dates, not greater than & less than. If that makes sense at all.
Thanks to you both for your help.
August 26, 2009 at 1:57 am
Nigel & Elliot (I got your names right this time), I tried Nigel's second suggested bit of code.
Except that I changed it from subtracting 3 milliseconds, to subtracting 1 day from the current date.
Also changed the query in the stored proc to look for data BETWEEN those dates, not greater than & less than. If that makes sense at all.
Thanks to you both for your help.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply