none
Same StoredProcdure giving different results at different time

    Question

  • Hi all,

    When i execute the same StoredProcedure , I am getting different results (Count of value is different )

    for first time i execute i am getting expected count

    but at the 3rd or some times i am getting expected .

    am not able to figure out

    Please help me


    Regards, Subathra


    Sunday, March 23, 2014 6:24 AM

All replies

  • Subathra, you have not provided anything to us to comment. Could you please share your procedure? If you can replicate the issue with small set of sample data and share with us, we would be able to help you better.
    Sunday, March 23, 2014 6:58 AM
  • Are you passing same set of parameters in all cases? Are you using cross database joins? Are you using NOLOCK hits /READ UNCOMMITED isolation?

    We can only guess at this stage as you've not shown us how the procedure looks or atleast a high level explanation of what it does.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, March 23, 2014 7:46 AM
  • No am not using same set of parameters and no lock and all.

    I have to write Stored procedure for the count of Incident Id bewteen sunday to saturday of one month

    i have taken whole data in one temp table

    and beween first sunday to saturday getting count and inserting it into table variable

    and same for 4 sundays to saturdays getting count and inserting it into table variable

    This is the scenario of my task.


    Regards, Subathra

    Sunday, March 23, 2014 11:34 AM
  • can you please paste your procedure code here with data?
    Sunday, March 23, 2014 11:52 AM
  • No am not using same set of parameters and no lock and all.

    I have to write Stored procedure for the count of Incident Id bewteen sunday to saturday of one month

    We need to see your query and table to accurately help you, otherwise, we can only make wild guesses.  Please post that information.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, March 23, 2014 11:58 AM
  • Everyone is right, we only can guess and I'm trying to do it. You say from sunday toi saturday grouped by month but in this case how do you handle the differents months/week? I mean, a month can start and finish on Wedsneday, Friday, Thursday...etc.

    It could be this the issue?

    Sunday, March 23, 2014 9:11 PM
  • Hi all,

    The structure of my sql query is like this

    I am using select * from #roottbl in another store procedure. There ill get whole date between start date and end date.

    Where as the below part am executing in another procedure.

    the counts are different.

    Select * into #Roottbl
    (
    Select *from incidentsimvw i where i.Createddate between @startdate and @enddate
    ) A
    Declare @week1startdate datetime
    Declare @week1enddate datetime

    Declare @week2startdate datetime
    Declare @week2enddate datetime
    Declare @table table
    (......    )
    Insert into @table
    Select *from #roottbl rt where rt.createddate between @week1startdate and @week1enddate
    Insert into @table
    Select * from #roottbl rt where rt.createddate between @week2startdate and @week2enddate

    Select *from @table

    Drop table #roottbl

    --------- Wrongly Typed

    When i execute multiple times am getting the same count.

    --------- Wrongly Typed

    When I execute thrice or fourth time am getting correct count, Else it wont return sometime

    please help me


    Regards, Subathra



    Sunday, March 30, 2014 12:59 PM
  • When i execute multiple times am getting the same count.

    Your original post mentioned a different count.  I assume the problem is that you are supplying the same values for @startdate and @enddate but a different count is returned?  Can you post the code where you are assigning the values for @week1startdate, @week1enddate, @week2startdate and @week2enddate?

    It is important to consider that a datetime data type includes time too.  So if the @startdate and @enddate values are derived from GETDATE(), you make get different rows every time the code is run unless you remove the time portion.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, March 30, 2014 1:38 PM
  • Hi all,

    The structure of my sql query is like this

    I am using select * from #roottbl in another store procedure. There ill get whole date between start date and end date.

    Where as the below part am executing in another procedure

    Select * into #Roottbl
    (
    Select *from incidentsimvw i where i.Createddate 

    ) A
    Declare @week1startdate datetime
    Declare @week1enddate datetime

    Declare @week2startdate datetime
    Declare @week2enddate datetime
    Declare @table table
    (......    )
    Insert into @table
    Select *from #roottbl rt where rt.createddate between @week1startdate and @week1enddate
    Insert into @table
    Select * from #roottbl rt where rt.createddate between @week2startdate and @week2enddate

    Select *from @table

    Drop table #roottbl

    When I execute thrice or fourth time am getting correct count, Else it wont return sometime

    please help me


    Regards, Subathra

    Sunday, March 30, 2014 4:09 PM
  • The code you posted has some syntax issues so i didnt understand how you were able to execute it first

    Anyways you've not shown as the core part ie how you're getting values for week start and end dates. Without that ts difficult to understand the issue as value is populated to @table based on those variable values.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, March 30, 2014 5:38 PM
  • Hi all,

    The structure of my sql query is like this

    I am using select * from #roottbl in another store procedure. There ill get whole date between start date and end date.

    Where as the below part am executing in another procedure

    Select * into #Roottbl
    (
    Select *from incidentsimvw i where i.Createddate 

    ) A
    Declare @week1startdate datetime
    Declare @week1enddate datetime

    Declare @week2startdate datetime
    Declare @week2enddate datetime
    Declare @table table
    (......    )
    Insert into @table
    Select *from #roottbl rt where rt.createddate between @week1startdate and @week1enddate
    Insert into @table
    Select * from #roottbl rt where rt.createddate between @week2startdate and @week2enddate

    Select *from @table

    Drop table #roottbl

    When I execute thrice or fourth time am getting correct count, Else it wont return sometime

    please help me


    Regards, Subathra

    You shall post the exact code... Encapsulate whats not important or you cant share, but atleast for answering, Forum needs some inputs.

    <Select * into #Roottbl (Select *from incidentsimvw i where i.Createddate ) A>

    This is wrong. I don't know how your code is running. <where i.Createddate >=What?


    Chaos isn’t a pit. Chaos is a ladder. Many who try to climb it fail and never get to try again. The fall breaks them. And some are given a chance to climb, but they refuse. They cling to the realm, or the gods, or love. Illusions. Only the ladder is real. The climb is all there is.

    Monday, March 31, 2014 10:12 AM
  • We're partly guessing, but chances are (as Dan pointed out) your issues vary when there are incidents that occurred on the final day of the date range, as a byproduct of the "midnight gotcha"... (I used to call it the "midnight bug", but it's not a bug, it's actually working exactly as intended).

    A Date or datetime value without a time component assumes the very first tick of the clock on that morning, so "2014/04/04" actually assumes "2014/04/04 00:00:00.000" in a query.

    And, '2014/04/04 09:15:00' < '2014/04/04' will fail...  It's 9 hours and 15 minutes GREATER than 2014/04/14.

    Declare @Example datetime = '2014-04-04 09:14:37.000'
    
    Declare @Criteria_without_a_time_component datetime = '2014/04/04'
    
    Select 'Something on the last day of the range with a time will not work as expected.' 
      Where @Example between '2014-01-01' and '2014/04/04'
    
    Select 'Most reliable way is to say "less than tomorrow".'
      where @Example > '2014/01/01' and @Example < DateAdd(day, 1, Cast('2014/04/04' as Date)) 
    Some people fabricate the end time to be the last tick of the clock ('2014/04/04 23:59:59.999') and still use "BETWEEN", but there is some sliver of a chance that some future version of SQL Server will accomodate picoseconds, and someday, a person's actual incident would occur at '2014/04/04 23:59:59.999000000001', and your query would break in the future.


    EDIT: To clarify... I suppose it still is a "midnight bug", but in cases like this, it's the programmer's bug, not a SQL Server bug... ;-)
    Monday, March 31, 2014 1:35 PM
  • Any resolution?

    >and beween first sunday to saturday getting count and inserting it into table variable

    As noted above, it is tricky to specify datetime ranges in WHERE clause predicates:

    http://www.sqlusa.com/bestpractices2008/between-dates/


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Wednesday, April 09, 2014 7:57 PM