Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
johnsonlim026
Starting Member
3 Posts |
 Posted - 2011-03-20 : 22:12:08
|
| Hi,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 A2).Select table B with condition B into temp table B3). Output of the stored procedure is the union result of temp table A and temp table B ( select * from temptableA union select * from temptableA)Result--------The no of records of Temp table A is 266530The no of records of Temp table B is 166The total no of records should be 266696My 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?HELP Please!! |
|
|
johnsonlim026
Starting Member
3 Posts |
Posted - 2011-03-20 : 23:31:46
|
Hi, i have find the solution.I should use "UNION ALL" instead of "UNION"Thanks all  |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-21 : 00:36:57
|
| UNION is like saying SELECT DISTINCT (with the accompanying overhead of distinct), while UNION ALL is not. |
|
|
|
|
|
|
No Of Record return form Stored Procedure is Not C