MSSQL Update中使用变量解决思路

declare @Row nvarchar(255)declare @i intselect @i=COUNT(*)from SRV_Rowwhile(@i>0)beginselect @Row=name from SRV_Row where [email protected]update SRV_SUB set @Row='yes'whereSRV_PART_NO in (select distinct w.SRV_PART_NO from SRV_SUB s inner join ['WINDOWS 7 32$'] w on s.SRV_PART_NO=w.SRV_PART_NO where [email protected])select @Rowset @[email protected]end在这里,我在update的使用,[email protected],[email protected]里,则变成了每循环一次,[email protected]求解决方法

declare @Row nvarchar(255)declare @sql text(8000)declare @i intselect @i=COUNT(*)from SRV_Rowwhile(@i>0)beginselect @Row=name from SRV_Row where [email protected]@sql ='update SRV_SUB set @Row='yes'whereSRV_PART_NO in (select distinct w.SRV_PART_NO from SRV_SUB s inner join ['WINDOWS 7 32$'] w on s.SRV_PART_NO=w.SRV_PART_NO where [email protected])'exec(@sql)select @Rowset @[email protected]end
------解决方案--------------------
设置一个字符串变量，拼接SQL执行语句，然后动态执行，exec  OR  sp_executesql
------解决方案--------------------
SQL code
declare @Row nvarchar(255)declare @sql text(8000)declare @i intselect @i=COUNT(*)from SRV_Rowwhile(@i>0)beginselect @Row=name from SRV_Row where [email protected]@sql ='update SRV_SUB set'+ @Row +' ='yes'whereSRV_PART_NO in (select distinct w.SRV_PART_NO from SRV_SUB s inner join ['WINDOWS 7 32$'] w on s.SRV_PART_NO=w.SRV_PART_NO where [email protected])'exec(@sql)select @Rowset @[email protected]end
------解决方案--------------------
SQL code
declare @Row nvarchar(255)declare @sql text(8000)declare @i intselect @i=COUNT(*)from SRV_Rowwhile(@i>0)beginselect @Row=name from SRV_Row where [email protected]@sql ='update SRV_SUB set'+ @Row +' =''yes''whereSRV_PART_NO in (select distinct w.SRV_PART_NO from SRV_SUB s inner join [''WINDOWS 7 32$''] w on s.SRV_PART_NO=w.SRV_PART_NO where [email protected])'exec(@sql)select @Rowset @[email protected]end
------解决方案--------------------
SQL code
exec('update SRV_SUB set'+ @Row+'='+quotename('yes','''')+'whereSRV_PART_NO in (select distinct w.SRV_PART_NO from SRV_SUB s inner join ['+quotename('WINDOWS 7 32$','''')+'] w on s.SRV_PART_NO=w.SRV_PART_NO where [email protected]+')'
------解决方案--------------------
SQL code
'update SRV_SUB set '+ @Row+'='+quotename('yes','''')+' where '+'SRV_PART_NO in (select distinct w.SRV_PART_NO from SRV_SUB s inner join ['+quotename('WINDOWS 7 32$','''')+'] w on s.SRV_PART_NO=w.SRV_PART_NO where [email protected]+')'