0

I have stored procedure which when I execute in 2 different databases on same server is returning different results.

e.g. in one DB it returns -4, -3.5, 3, -2.5, -2, -1,0

while in other DB it is returning 3, -4, 2.5, -1, -1.5

This has started happening recently. Initially we noticed this in our prod DB but our all test databases were returning correct output which was different from prod db. After we refresh our one test db with prod, the procedure result in test db also changed to incorrect output. Any clues what might be happening or what might have got changed in database level which caused same procedure to return different output.

Procedure is having use of temp tables in case this might have caused something.

Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
user2406368
  • 1
  • 3
    show some code ...... – JohnD Nov 05 '13 at 15:50
  • 3
    Different results or different order of results? – Martin Smith Nov 05 '13 at 15:50
  • 1
    Well, there's obviously not an `ORDER BY` clause or it's not specific enough or it relies on some setting that's different between the servers - until we can see the query and that `ORDER BY` clause, we'd just be guessing. – Damien_The_Unbeliever Nov 05 '13 at 15:55
  • @MartinSmith : Yes..its same result for both database but order of result is different. both database are on same server running same exact procedure. – user2406368 Nov 05 '13 at 16:01
  • 3
    So you need an `ORDER BY` on the select that produces the results that gives you the desired order. Without this no fixed order is guaranteed. – Martin Smith Nov 05 '13 at 16:04

1 Answers1

4

Yes..its same result for both database but order of result is different. both database are on same server running same exact procedure.

Always use an ORDER BY clause if you want your rows to be returned in a specific order. In SQL there is simply no other way to order your rows and no reason to avoid the clause, regardless of what may seem to work.

More info: http://tkyte.blogspot.com/2005/08/order-in-court.html

Similar SO questions with essentially the same answer:

Community
  • 1
  • 1
Tim Lehner
  • 14,813
  • 4
  • 59
  • 76