经典问题—“必须声明变量”

Question

create proc up_GetUndoneTasks
@Code nvarchar(24),
@PageIndex int,
@PageSize int
as
declare @t1 table
(
   MS_Code nvarchar(24),
   IR_Time datetime
) 
declare @t2 table
(
   MS_Code nvarchar(24),
   MS_Name nvarchar(50),
   IR_Time datetime,
   MS_InspectCycle real,
   IR_Overdue int
) 
declare @sql nvarchar(1000)
begin
   insert into @t1(MS_Code,IR_Time) select MS_Code,max(IR_Time) from InspectRecord group by MS_Code
   insert into @t2(MS_Code,MS_Name,IR_Time,MS_InspectCycle,IR_Overdue) select
ms.MS_Code,ms.MS_Name, 
ir.IR_Time,
ms.MS_InspectCycle,
        (datediff(hour,ir.IR_Time,getdate()) - ms.MS_InspectCycle) as IR_Overdue 
   from MonitorSpotInfo ms, @t1 ir 
   where ms.MS_Code = ir.MS_Code and 
   ms.MS_InspectCycle < (datediff(hour,ir.IR_Time,getdate())) and 
   ir.IR_Time < convert(nvarchar(10),getdate(),120) and ir.MS_Code like @Code+'%'  
    
   set @sql = 'select top '+str(@PageSize)+' MS_Code,convert(nvarchar(16),IR_Time,120) as IR_Time,MS_InspectCycle,IR_Overdue from '+str(@t2)+ 
' where MS_Code not in (select top '+str(@PageSize*(@PageIndex-1))+' MS_Code from '+str(@t2)+')'
exec(@sql)
end
GO服务器: 消息 137，级别 15，状态 2，过程 up_GetUndoneTasks，行 32
必须声明变量 '@t2'。

Answer 1

表变量不是这么用的，换成临时表吧：
create proc up_GetUndoneTasks
@Code nvarchar(24),
@PageIndex int,
@PageSize int
as
begin
    create table #t1(MS_Code nvarchar(24),IR_Time datetime) 
    
    create table #t2(MS_Code nvarchar(24),MS_Name nvarchar(50),IR_Time datetime,MS_InspectCycle real,IR_Overdue int) 
    
    declare @sql nvarchar(1000)
    
    begin
        insert into #t1(MS_Code,IR_Time) select MS_Code,max(IR_Time) from InspectRecord group by MS_Code
       
        insert into #t2(MS_Code,MS_Name,IR_Time,MS_InspectCycle,IR_Overdue) 
        select
            ms.MS_Code,ms.MS_Name, 
            ir.IR_Time,
            ms.MS_InspectCycle,
            (datediff(hour,ir.IR_Time,getdate()) - ms.MS_InspectCycle) as IR_Overdue 
        from 
            MonitorSpotInfo ms, #t1 ir 
        where 
            ms.MS_Code = ir.MS_Code 
            and 
            ms.MS_InspectCycle < (datediff(hour,ir.IR_Time,getdate())) 
            and 
            ir.IR_Time < convert(nvarchar(10),getdate(),120) and ir.MS_Code like @Code+'%'  
        
        set @sql = 'select top '+str(@PageSize)+' MS_Code,convert(nvarchar(16),IR_Time,120) as IR_Time,MS_InspectCycle,IR_Overdue from #t2 '+
                   'where MS_Code not in (select top '+str(@PageSize*(@PageIndex-1))+' MS_Code from #t2)'
       
        exec(@sql)
    end
end
GO

Answer 2

表变量@t2对于
set @sql = 'select top '+str(@PageSize)+' MS_Code,convert(nvarchar(16),IR_Time,120) as IR_Time,MS_InspectCycle,IR_Overdue 
from '+str(@t2)+' where MS_Code not in (select top '+str(@PageSize*(@PageIndex-1))+' MS_Code from '+str(@t2)+')'可见。
但是对于exec(@sql)内部的@sql语句，不可见。可以考虑用临时表替代。

Answer 3

create proc up_GetUndoneTasks
    @Code nvarchar(24),
    @PageIndex int,
    @PageSize int
as begin
    declare @t1 table (
       MS_Code nvarchar(24),
       IR_Time datetime
    ) 
    declare @t2 table (
       idx int identity(1,1),
       MS_Code nvarchar(24),
       MS_Name nvarchar(50),
       IR_Time datetime,
       MS_InspectCycle real,
       IR_Overdue int
    ) 
    declare @sql nvarchar(1000)
    begin
        insert into @t1(MS_Code,IR_Time) 
        select MS_Code,max(IR_Time) 
        from InspectRecord 
        group by MS_Code        insert into @t2 (MS_Code,MS_Name,IR_Time,MS_InspectCycle,IR_Overdue) 
        select
            ms.MS_Code,
            ms.MS_Name, 
            ir.IR_Time,
            ms.MS_InspectCycle,
            (datediff(hour,ir.IR_Time,getdate()) - ms.MS_InspectCycle) as IR_Overdue 
        from MonitorSpotInfo ms, @t1 ir 
        where ms.MS_Code = ir.MS_Code 
        and ms.MS_InspectCycle < (datediff(hour,ir.IR_Time,getdate())) 
        and ir.IR_Time < convert(nvarchar(10),getdate(),120) 
        and ir.MS_Code like @Code+'%' 
        
        select 
            MS_Code,
            convert(nvarchar(16),IR_Time,120) as IR_Time,
            MS_InspectCycle,
            IR_Overdue 
        from @t2
        where idx between @PageSize*(@PageIndex-1)+1 and @PageSize*(@PageIndex) 
end
go

Answer 4

(3L的思路说明)..临时表是个解决方式。因为表变量不能传递到动态脚本中使用。
其实这个proc的动态脚本是为了实现"分页"，那么可以考虑换一种分页的计算方式。..

Answer 5

create proc up_GetUndoneTasks(--漏了(
@Code nvarchar(24),
@PageIndex int,
@PageSize int)--漏了)
as
declare @t1 table
(
   MS_Code nvarchar(24),
   IR_Time datetime
) 
declare @t2 table
(
   MS_Code nvarchar(24),
   MS_Name nvarchar(50),
   IR_Time datetime,
   MS_InspectCycle real,
   IR_Overdue int
) 
declare @sql nvarchar(1000)
begin
   insert into @t1(MS_Code,IR_Time) select MS_Code,max(IR_Time) from InspectRecord group by MS_Code
   insert into @t2(MS_Code,MS_Name,IR_Time,MS_InspectCycle,IR_Overdue) select
    ms.MS_Code,ms.MS_Name, 
    ir.IR_Time,
    ms.MS_InspectCycle,
        (datediff(hour,ir.IR_Time,getdate()) - ms.MS_InspectCycle) as IR_Overdue 
   from MonitorSpotInfo ms, @t1 ir 
   where ms.MS_Code = ir.MS_Code and 
   ms.MS_InspectCycle < (datediff(hour,ir.IR_Time,getdate())) and 
   ir.IR_Time < convert(nvarchar(10),getdate(),120) and ir.MS_Code like @Code+'%'  
    
   set @sql = 'select top '+str(@PageSize)+' MS_Code,convert(nvarchar(16),IR_Time,120) as IR_Time,MS_InspectCycle,IR_Overdue from '+str(@t2)+ 
' where MS_Code not in (select top '+str(@PageSize*(@PageIndex-1))+' MS_Code from '+str(@t2)+')'
exec(@sql)
end
GO

Answer 6

表变量str一下成什么东东了？

Answer 7

楼主主要是为了分页吧.在sql server 2005之前 top 后面不能直接跟变量的,所以要用动态sql.如果是2005,2008可以直接在top后面使用变量.就不需要动态sql了. 如果是2000.那表变量就改成临时表.表变量和临时表生命周期不一样.变量的生命周期是同一个批处理内.临时表(非全局临时表)是同一个连接内
2005,2008举例.注意变量要用()扩起来.
declare @a int
set @a = 10select top (@a) * from table