2 zhaojingjings zhaojingjings 于 2013.08.27 15:12 提问

存储过程创建临时表(表名是动态的),游标中使用了这一个临时表

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 行
批处理执行由于调试器请求而终止。

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!