Unanswered: No Of Record return form Stored Procedure is Not Consistent
I have a stored procedure that return me inconsistent no. of records.
The stored procedure condition is fixed and the table involved in the stored procedure is static. No one except me using the tables.
This is how my stored procedure work.
1).Select table A with condition A into temp table A
2).Select table B with condition B into temp table B
3). Output of the stored procedure is the union result of temp table A and temp table B ( select * from temptableA union select * from temptableA)
The no of records of Temp table A is 266530
The no of records of Temp table B is 166
The total no of records should be 266696
My problem is
if the stored procedure return only the records of temp table A or temp tableB itself it is correct.
If the stored procedure return the union result of temp table A and temp table B,it become incorrect. The no of records return is only 266500. 196 records is truncated.
I have try a work around that is i insert the union result into a database table and then select the data out in the stored procedure.It return me the correct no of records.
I have tested that also the stored procedure able to return result of 266696 records.
My question is why can this error happen?
Does anyone has idea on this?Is there any way to solve this error by not inserting record in database table and then select it out?