编译不报错，运行产生 “*****列名无效”的错误

 

declare @tbname varchar(50)

declare v_cur cursor for select tbname from dbo.tbs

declare @sql nvarchar(4000)

declare @dbname varchar(50)

set @dbname = 'GTA_BTR'

open v_cur

fetch v_cur into @tbname

while @@fetch_status = 0

 begin

   set @sql='update dbo.tbs set csum =(select count(*) from '+@tbname+') where tbname ='+@tbname+' and dbname = '+@dbname

   exec(@sql)

   fetch v_cur into @tbname

 end

close v_cur

deallocate v_cur

编译不会出错，运行到红色标明的语句时会报列名无效的错误

原因：当把@sql语句用print打印出来时会发现语句是这样的

update dbo.tbs set csum =(select count(*) from BTR_Bondmkt1) where tbname =BTR_Bondmkt1 and dbname = GTA_BTR

而在数据库中tbname和dbname两列均是varchar类型  故编译器会将BTR_Bondmkt1和GTA_BTR当做列名处理

解决方案：就是想办法将打印语句中BTR_Bondmkt1和GTA_BTR 都加上一对‘’号。

正确的写法是

set @sql='update dbo.tbs set csum =(select count(*) from '+@tbname+') where tbname ='''+@tbname+''' and dbname = '''+@dbname+''''

其打印的效果将是：

update dbo.tbs set csum =(select count(*) from BTR_Bondmkt1) where tbname ='BTR_Bondmkt1' and dbname = 'GTA_BTR'