求一段sql 拼接方法 双循环 存储过程
create procedure AA
as begin
declare @_i int
declare @_len int
select @_len=max(len(cityname)) from dbo.T_city
set @_i=1
while @_i <= @_len
begin
select substring(cityname, @_i,1) from dbo.T_city
set @_i=@_i+1
end
end
exec cit_name
select substring(cityname, @_i,1) from dbo.T_city 这句话是分表显示的,怎么循环起来
如:select a a1 a2... from dbo.T_city 把substring(cityname, @_i,1) @_i的值按每次提取不一条件拼接成一个语句


[解决办法]

SQL code

create procedure AAas   begin    declare @s varchar(1000)    set @s = 'select '    declare @_i int    declare @_len int    select @_len=max(len(cityname)) from dbo.T_city      set @_i=1      while @_i <= @_len      begin        set @s = @s + ' substring(cityname , ' + ltrim(@_i) + ' , ) ,'        set @_i=@_i+1      end    set @s = substring(@s , 1 , len(@s) - 1) + ' from dbo.T_city'    exec cit_name  end
[解决办法]
--如果你的不够@_len怎么办?,也许下面的好点.
SQL code
create procedure AAas   begin    declare @s varchar(1000)    set @s = 'select '    declare @_i int    declare @_len int    select @_len=max(len(cityname)) from dbo.T_city      set @_i=1      while @_i <= @_len      begin        set @s = @s + ' substring(cityname , ' + ltrim(@_i) + ' , ) ,'        set @_i=@_i+1      end    set @s = substring(@s , 1 , len(@s) - 1) + ' from dbo.T_city where len(cityname) >= ' + ltrim(@_len)    exec cit_name  end