set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go ALTER procedure [dbo].[GetField] @tablename char(30), @alias varchar(10) = ''
as
declare @list varchar(500), @list1 varchar(500) -- 长度不够时用list1
select @list = ''
select @list1=''
declare @id int
declare @fieldname varchar(30)
select @id = id from sysobjects where name = @tablename
declare mycur cursor for select name from syscolumns where id = @id
open mycur
fetch next from mycur into @fieldname
WHILE (@@FETCH_STATUS = 0 )
BEGIN
if @alias <> ''
begin
select @fieldname = @alias + '.' + @fieldname
end
if (len(@list1)>0) or (len(@list)+len(@fieldname)>252)
begin
select @list1 = @list1 + @fieldname
select @list1 = @list1 + ', '
end
else
begin
select @list = @list + @fieldname
select @list = @list + ', '
end
fetch next from mycur into @fieldname
END
close mycur
deallocate mycur
if len(@list1)>0
select @list1 = substring(@list1, 1, len(@list1)-1)
else
select @list = substring(@list, 1, len(@list)-1)
select @list, @list1这个存储过程是怎么解释的,有人知道吗?
set QUOTED_IDENTIFIER ON
go ALTER procedure [dbo].[GetField] @tablename char(30), @alias varchar(10) = ''
as
declare @list varchar(500), @list1 varchar(500) -- 长度不够时用list1
select @list = ''
select @list1=''
declare @id int
declare @fieldname varchar(30)
select @id = id from sysobjects where name = @tablename
declare mycur cursor for select name from syscolumns where id = @id
open mycur
fetch next from mycur into @fieldname
WHILE (@@FETCH_STATUS = 0 )
BEGIN
if @alias <> ''
begin
select @fieldname = @alias + '.' + @fieldname
end
if (len(@list1)>0) or (len(@list)+len(@fieldname)>252)
begin
select @list1 = @list1 + @fieldname
select @list1 = @list1 + ', '
end
else
begin
select @list = @list + @fieldname
select @list = @list + ', '
end
fetch next from mycur into @fieldname
END
close mycur
deallocate mycur
if len(@list1)>0
select @list1 = substring(@list1, 1, len(@list1)-1)
else
select @list = substring(@list, 1, len(@list)-1)
select @list, @list1这个存储过程是怎么解释的,有人知道吗?
则执行 exec GetField 'TAA'结果是:
部门, 编号, 日期, 数量 则执行 exec GetField 'TAA','TAA'
结果是:
TAA.部门, TAA.编号, TAA.日期, TAA数量所以他的意思是遍历一个表的所有列。
则执行 exec GetField 'TAA','别名'
结果是:
别名.部门,别名.编号, 别名.日期,别名.数量