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