February 6, 2008 at 6:11 pm
Hi i am trying to run stored proc but getting confuse
i have stored proc that has parameter of startdate, enddate, and type
i need to run for 2 days before
exec testproc getdate()-2,getdate()-2,All
then i get 0 result
but if i run
exec testproc "02/05/2008","02/05/2008",'All'
then i get 50 result back
what i am doing wrong??
Help!!
February 6, 2008 at 6:27 pm
First, use the dateadd function instead of trying to subtract an integer from a date (i.e. use dateadd(day, -2, getdate()) instead of getdate()-2) to make sure that the parameter you're calculating matches the stored procedure's input parameter expectations. Also, in your example, you neglected to include the single quotes around All ('All') when passing in that string from your code, though they were there for your manual data entry. Those two changes should make the procedure work the same in both cases.
February 6, 2008 at 7:38 pm
i tried that too
exec testproc dateadd(day, -2, getdate()),dateadd(day, -2, getdate()),'all'
but it gives me error at
that in corect syntax near day
what am i missing!!
February 6, 2008 at 7:56 pm
I also tried
declare @StartDate datetime, @EndDate datetime
set @Startdate = dateadd(day, -2, getdate())
set @Enddate = dateadd(day, -2, getdate())
EXEC testproc @Startdate,@Startdate, 'All'
but resule
0 0 0
while if i do
exec testproc '2/5/2008','2/5/2008','All'
1 50 -48
February 6, 2008 at 7:57 pm
Look up dateadd in BOL for the syntax.
February 7, 2008 at 12:37 am
Hi pat,
This may help you to sort out the problem. I believe 😛
CREATE PROCEDURE Test
(
@SDate DateTime,
@EDate DateTime,
@status varchar(20)
)
AS
BEGIN
SELECT @SDate
SELECT @EDate
SELECT CAST(@Status AS varchar)
END
DECLARE @StartDate datetime, @EndDate datetime
SET @Startdate = dateadd(day, -2, getdate())
SET @Enddate = dateadd(day, -2, getdate())
EXEC Test @StartDate,@EndDate,'ALL'
EXEC Test '2008-02-05 13:12:45.823','2008-02-05 13:12:45.823','ALL'
---
February 7, 2008 at 1:56 am
Hi patpat,
You want to pass date parameter to SP.
SP get date in as string and because it evaluate this in a valid date formate so if you want to pass it 2 day's before then save it in a variable and then pass like this.
Declare @vDate Datetime
Set @vDate = getdate() -2 you can also use datadd
exec test @vDate
in your sp it must work
February 7, 2008 at 8:54 am
I tend to suspect that the reason for the difference in results is that getdate() includes the time of day, while the dates you manually typed in were both midnight.
If, for example, the proc includes a Where clause like "Where date between @param1 and @param2", and all the records are stored as midnight, and you run the proc at anything except exactly midnight, you won't get any results.
If, on the other hand, you use timeless dates, like the second example, you'd get all the records for that date.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply