日期:2014-05-18 浏览次数:20550 次
declare @tableName nvarchar(max) set @tableName='table1,table2,table3,table4' select substring(@tableName,number,charindex(',',@tableName+',',number)-number) tbn from master..spt_values where [type] = 'p' and number between 1 and len(@tableName) and substring(','+@tableName,number,1) = ',' /********************************* tbn ---------------------------------------------------------------------------------------------------------------- table1 table2 table3 table4 (4 行受影响)
------解决方案--------------------
declare @tableName nvarchar(max) declare @sql varchar(max) declare @DocEntry nvarchar(max) set @tableName='table1,table2,table3,table4' set @DocEntry = 'opq' select @sql = isnull(@sql,'')+' delete from ' + tbn + ' where DocEntry = ''' + @DocEntry + '''' + char(13) from( select substring(@tableName,number,charindex(',',@tableName+',',number)-number) tbn from master..spt_values where [type] = 'p' and number between 1 and len(@tableName) and substring(','+@tableName,number,1) = ',' )t print @sql /******************** delete from table1 where DocEntry = 'opq' delete from table2 where DocEntry = 'opq' delete from table3 where DocEntry = 'opq' delete from table4 where DocEntry = 'opq'
------解决方案--------------------
declare @tableName nvarchar(max) declare @i int=1 set @tableName='table1,table2,table3,table4' while(@i<=len(','+@tableName)) begin if substring(','+@tableName,@i,1)=',' select substring(','+@tableName,@i+1,charindex(',',@tableName+',',@i+1)-@i) tb select @i=@i+1 end -- 结果 tb -------- table1 tb -------- table2 tb -------- table3 tb -------- table4