执行select显示某个字段的内容，怎样分行显示该内容？

[liuzixi]

由于该字段是text类型，内容很长，结果在一行中看，很不方便

[dawugui]

这不是SQL的功能吧?

自己在前台通过软件来做.

[areswang]

--测试表
if exists (select 1 from sysobjects where name='tb' and type='u')
drop table tb
create table tb (id int ,text varchar(8000))
insert into tb select 1,'abcdefghijklmnopqrstuvwxyz'
union select 2 ,'测试数据,测试数据测试数据测试数据'
union select 3 ,'可将函数和变量声明为 table 类型。table 变量可用于函数、存储过程和批处理中。'

--创建存储过程
if exists (select 1 from sysobjects where name='getrowtext' and type='p')
drop proc getrowtext
go
create proc getrowtext (@len int) as
begin
declare @pos int,
@i int,
@times int,
@id int,
@text varchar(255)
declare @tmptb table(id int, text varchar(255))
declare tb_cursor cursor for select id, text from tb order by id
open tb_cursor
fetch next from tb_cursor into @id, @text
while @@fetch_status = 0
begin
set @i=1
set @pos=1
set @times=len(@text)/@len+1
while @i<=@times
begin
insert into @tmptb select @id, substring(@text,@pos,@len)
set @pos=@pos+@len
set @i=@i+1
end
fetch next from tb_cursor into @id, @text
end
close tb_cursor
deallocate tb_cursor
select * from @tmptb
end

--测试数据
exec getrowtext 10

[qqhmitzk]

2楼用关键字做字段名,不妥把
能调试通过

[qqhmitzk]

改下2楼的

if   exists   (select   1   from   sysobjects   where   name='tb'   and   type='u')     
  drop   table   tb   
go
  create   table   tb   (id1   int   , test varchar(8000))   
  insert   into   tb   select   1,'abcdefghijklmnopqrstuvwxyz'   
  union   select   2   ,'测试数据,测试数据测试数据测试数据'   
  union   select   3   ,'可将函数和变量声明为   table   类型。table   变量可用于函数、存储过程和批处理中。'   
go
if   exists   (select   1   from   sysobjects   where   name='getrowtext'   and   type='p')     
  drop   proc   getrowtext   
  go   
  create   proc   getrowtext   (@len   int)   as     
  begin   
  declare   @pos   int,   
  @i   int,   
  @times   int,   
  @id   int,   
  @text   varchar(255)   
  declare @tmptb   table(id1   int,   test   varchar(255))   
  declare   tb_cursor   cursor   for   select   id1,   test   from   tb   order   by   id1   
  open   tb_cursor   
  fetch   next   from   tb_cursor   into   @id,   @text   
  while   @@fetch_status   =   0   
  begin   
  set   @i=1   
  set   @pos=1   
  set   @times=len(@text)/@len+1   
  while   @i <=@times   
  begin   
  insert   into   @tmptb   select   @id,   substring(@text,@pos,@len)   
  set   @pos=@pos+@len   
  set   @i=@i+1   
  end   
  fetch   next   from   tb_cursor   into   @id,   @text   
  end   
  close   tb_cursor   
  deallocate   tb_cursor   
  select   *   from   @tmptb   
  end   

最好用程序控制,SQL不是万能的

[leisure_cool]

学习