Results 1 to 7 of 7

Thread: Same SQL query produces two different results

  1. #1
    Join Date
    Nov 2006
    Posts
    42

    Question Same SQL query produces two different results

    This is a strange one. I have a stored procedure that determines a count of the number of records that meet certain criteria. If I call the stored procedure in a Query Analyzer window by using "exec sp_[procedurename]" I get a count of 1. If I copy the SQL from the stored procedure, paste it into the Query Analyzer window and run it, I get a count of 0. The 0 value is correct.

    There is no rounding involved in the query. In a general sense, can anyone explain why the same exact query on the same database would return two different results?

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Hard to tell without reading code, but did you compare execution plan?

  3. #3
    Join Date
    Nov 2006
    Posts
    42
    I'm not much good at reading those but I do see some differences. I've attached both. "SP" is the plan generated when calling the SP by name. "Query" is the plan generated by running the code in a QA window.

    In general, why would a stored procedure execute differently if called by name than if you run the code in a QA window?
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    You can get graphic plan, easier to read. By the way, look like have different plans.

  5. #5
    Join Date
    Nov 2006
    Posts
    42
    Yes, that's what is so strange. It's the same code! The only difference between the two is that in one case I am executing the stored procedure by calling it; in the other I pasted all the code from the SP into a QA window and ran it that way.
    Attached Files Attached Files

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Update statistics on tables then try again.

  7. #7
    Join Date
    Nov 2006
    Posts
    42
    I was about to do that when the issue appeared to resolve itself. I'll have to watch and see if it happens again, then try your suggestion. Thanks (again) for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •