TOP 1 in select statement with different results.
-
Hi, Are there any issues using TOP clause in temp tables in stored procedure? We are experiencing some difficulties for which we are unable to debug. We are receiving two sets of different results, for same input parameters. When we run stored proc the first time versus when it is run 5 minutes later, the results are not consistent. We are assuming using the TOP 1 clause may have some issues. Thanks for your time.
-
Hi, Are there any issues using TOP clause in temp tables in stored procedure? We are experiencing some difficulties for which we are unable to debug. We are receiving two sets of different results, for same input parameters. When we run stored proc the first time versus when it is run 5 minutes later, the results are not consistent. We are assuming using the TOP 1 clause may have some issues. Thanks for your time.
Do you have an "order by" clause on your select statement?
-
Do you have an "order by" clause on your select statement?
-
Can you post the code for the stored procedure to this forum? There are no issues with doing a "top 1" on temporary tables - apart from the fact the SQL-Server may possibly use a different query-plan each time you run it. However, your "order by" clause should have fixed that. Is it possible that your temporary table somehow contains different data for each of the runs? I have had this caused by an update or delete statement using different query plans for different runs.
-
Can you post the code for the stored procedure to this forum? There are no issues with doing a "top 1" on temporary tables - apart from the fact the SQL-Server may possibly use a different query-plan each time you run it. However, your "order by" clause should have fixed that. Is it possible that your temporary table somehow contains different data for each of the runs? I have had this caused by an update or delete statement using different query plans for different runs.
Here you go. -------------- select top 1 @lastdrive = t1.datetimestamp from #e t1 inner join #r r1 on r1.routestart <= t1.datetimestamp and r1.routeend >= t1.datetimestamp where t1.eventnum = 1108 and t1.datetimestamp >= @stopstart and t1.datetimestamp <= @stopend and r1.routedatekey = @routedatekey and t1.driverkey = r1.driverkey and r1.driverkey = @driverkey order by t1.datetimestamp desc -------------- This is run within a cursor. Also - does it make a difference if we used something like -- select @lastdrive = max (t1.datetimestamp) -- instead? Thanks. Vani
-
Here you go. -------------- select top 1 @lastdrive = t1.datetimestamp from #e t1 inner join #r r1 on r1.routestart <= t1.datetimestamp and r1.routeend >= t1.datetimestamp where t1.eventnum = 1108 and t1.datetimestamp >= @stopstart and t1.datetimestamp <= @stopend and r1.routedatekey = @routedatekey and t1.driverkey = r1.driverkey and r1.driverkey = @driverkey order by t1.datetimestamp desc -------------- This is run within a cursor. Also - does it make a difference if we used something like -- select @lastdrive = max (t1.datetimestamp) -- instead? Thanks. Vani
I would use the
@lastdrive = max(t1.datetimestamp)method. It may allow you to remove the cursor from your code. -
Hi, Are there any issues using TOP clause in temp tables in stored procedure? We are experiencing some difficulties for which we are unable to debug. We are receiving two sets of different results, for same input parameters. When we run stored proc the first time versus when it is run 5 minutes later, the results are not consistent. We are assuming using the TOP 1 clause may have some issues. Thanks for your time.
You need to use ## for a global temporary table. A temporary table whose name begins with a single # is a local temporary table and is local to the connection. SQL Server 2005 documentation says: "There are two types of temporary tables: local and global. They differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server."
Stability. What an interesting concept. -- Chris Maunder