问题描述

存储过程创建临时表（表名是动态的），游标中使用了这一个临时表

ALTER PROCEDURE [dbo].[P_GetBusLocation]
@Date1 datetime
AS
BEGIN
SET NOCOUNT ON;
declare @VehicleNo nvarchar(50),@VGroupID int,@RouteNumber varchar(200),@TableName varchar(50),@VehicleID bigint ,
@OldVehicleID bigint,@SqlStr1 varchar(1024),@SqlStr2 varchar(1024),@TemTableName varchar(50),@SqlStr3 varchar(1024)
set @TableName = 'gps005' + CONVERT(varchar(20), @Date1, 112)
set @TemTableName = '#Tem' + @TableName
set @SqlStr1 = 'create Table ' + @TemTableName+'(GpsDateTime datetime,VehicleID bigint,VehicleNo nvarchar(50),RouteNumber varchar(200),Flag tinyint
,Longitude decimal(9,6),Latitude decimal(8,6),Speed decimal(4,1),Direction smallint,Mileage bigint
,Position varchar(200),AvgSpeed decimal(4,1),StorageTime datetime)'
exec(@SqlStr1)

set @SqlStr2 = 'insert into '+@TemTableName+' select t1.GpsDateTime,t1.VehicleID,null,null,t1.Flag
,t1.Longitude,t1.Latitude,t1.Speed,t1.Direction,t1.Mileage,t1.Position,t1.AvgSpeed,t1.StorageTime from '+@TableName +' as t1'

exec(@SqlStr2)

set @OldVehicleID = 0
set @SqlStr3 = 'declare myCursor cursor FORWARD_ONLY STATIC for select VehicleID from '+@TemTableName
exec(@SqlStr3)
open myCursor
fetch next from myCursor into @VehicleID
while(@@FETCH_STATUS = 0)
begin

if(@VehicleID != @OldVehicleID)
begin
select @VGroupID = VGroupID,@VehicleNo = VehicleLic from VehicleInfo where VehicleID = @VehicleID
select @RouteNumber = RouteNumber FROM RouteInfo where VGroupID = @VGroupID
exec('update '+@TemTableName+' set VehicleNo = '+@VehicleNo+',RouteNumber = '+@RouteNumber+' where VehicleID = '+@VehicleID)
set @OldVehicleID = @VehicleID
end
fetch next from myCursor into @VehicleID
end
exec('select * from '+ @TemTableName)
close myCursor
deallocate myCursor
END

入参是：2013-8-8
下面是报错：
消息 208，级别 16，状态 0，第 1 行
对象名 '#Temgps00520130808' 无效。
消息 208，级别 16，状态 0，第 1 行
对象名 '#Temgps00520130808' 无效。
消息 16916，级别 16，状态 1，过程 P_GetBusLocation，第 35 行
名为 'myCursor' 的游标不存在。
消息 16916，级别 16，状态 1，过程 P_GetBusLocation，第 38 行
名为 'myCursor' 的游标不存在。
消息 28102，级别 16，状态 1，过程 P_GetBusLocation，第 39 行
批处理执行由于调试器请求而终止。