declare @tableName nvarchar(max)
declare @i int
set @tableName='table1,table2,table3,table4'
while @i>0
begin
[email protected] table2 table3 table4分别截取出来,如何实现，并且能够结束循环
end
------解决方案--------------------
- SQL code
declare @tableName nvarchar(max)set @tableName='table1,table2,table3,table4'select substring(@tableName,number,charindex(',',@tableName+',',number)-number) tbnfrom master..spt_valueswhere [type] = 'p' and number between 1 and len(@tableName) and substring(',[email protected],number,1) = ','/*********************************tbn----------------------------------------------------------------------------------------------------------------table1table2table3table4(4 行受影响)
------解决方案--------------------
- SQL code
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(',[email protected],number,1) = ',')tprint @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'
------解决方案--------------------
- SQL code
declare @tableName nvarchar(max)declare @i int=1set @tableName='table1,table2,table3,table4'while(@i<=len(',[email protected]))begin if substring(',[email protected],@i,1)=',' select substring(',[email protected],@i+1,charindex(',',@tableName+',',@i+1)[email protected]) tb select @[email protected]+1end-- 结果tb--------table1tb--------table2tb--------table3tb--------table4