create proc pt @id int, @retval nvarchar(max) output as begin declare @sql nvarchar(max); select @sql=isnull(@sql,'select @retval=')+c.name+'+''|''+' from syscolumns c,sysobjects o,systypes t where c.id =o.id and o.name='TAB1' and c.xtype=t.xtype and t.name in('char','varchar','nchar','nvarchar'); select @sql=substring(@sql,1,len(@sql)-5)+' from TAB1 where ID='+cast(@id as nvarchar(20)); exec sp_executesql @sql,N'@retval nvarchar(max) output',@retval output end godeclare @retval nvarchar(max) exec pt 1,@retval output select @retval;
--取字段 create table testtb (id int identity(1,1),A varchar(30),B varchar(30),C varchar(30)) insert into testtb(A,B,C) select 'a','b','c' insert into testtb(A,B,C) select 'aa','bb','cc' declare @s varchar(max) set @s ='' SELECT @s += NAME +'|' FROM syscolumns WHERE ID=OBJECT_ID('testtb') SELECT @s drop table testtb go --或者(取内容) create table testtb (id int identity(1,1),A varchar(30),B varchar(30),C varchar(30)) insert into testtb(A,B,C) select 'a','b','c' insert into testtb(A,B,C) select 'aa','bb','cc' declare @s varchar(max) declare @sql nvarchar(max) set @sql='' set @s =''--必须初始化 SELECT @s += NAME +'+'+'''|''' +'+'FROM syscolumns WHERE ID=OBJECT_ID('testtb') set @s = SUBSTRING(@s,0,LEN(@s)) set @s = SUBSTRING(@s,CHARINDEX('+',@s,0)+5,len(@s)) select @sql =N'select '+@s +' from testtb where id =2' --根据ID查询 exec (@sql) drop table testtb
--那只能自己手动加你要的列再动态查询了 参考: create table testtb (id int identity(1,1),A1 INT,A2 DATETIME,A3 varchar(30),A4 VARCHAR(30)) insert into testtb(A1,A2,A3) select '1',GETDATE(),'c' insert into testtb(A1,A2,A3) select '11',GETDATE(),'cc' declare @s varchar(max)='CAST(A1 AS varchar(30))+''|''+CAST(A2 AS varchar(30))+''|''+A3+''|'''--手动添加所有要的列(因为你最终要的是字符串所以其他类型的都可以先转为字符的) declare @sql nvarchar(max) set @sql='' select @sql =N'select '+@s +' from testtb where id =2' --根据ID查询 exec (@sql)--动态执行 drop table testtb
create proc pt
@id int,
@retval nvarchar(max) output
as
begin
declare @sql nvarchar(max);
select @sql=isnull(@sql,'select @retval=')+c.name+'+''|''+'
from syscolumns c,sysobjects o,systypes t
where c.id =o.id and o.name='TAB1'
and c.xtype=t.xtype and t.name in('char','varchar','nchar','nvarchar');
select @sql=substring(@sql,1,len(@sql)-5)+' from TAB1 where ID='+cast(@id as nvarchar(20));
exec sp_executesql @sql,N'@retval nvarchar(max) output',@retval output
end
godeclare @retval nvarchar(max)
exec pt 1,@retval output
select @retval;
create table testtb (id int identity(1,1),A varchar(30),B varchar(30),C varchar(30))
insert into testtb(A,B,C)
select 'a','b','c'
insert into testtb(A,B,C)
select 'aa','bb','cc'
declare @s varchar(max)
set @s =''
SELECT @s += NAME +'|' FROM syscolumns WHERE ID=OBJECT_ID('testtb')
SELECT @s
drop table testtb
go
--或者(取内容)
create table testtb (id int identity(1,1),A varchar(30),B varchar(30),C varchar(30))
insert into testtb(A,B,C)
select 'a','b','c'
insert into testtb(A,B,C)
select 'aa','bb','cc'
declare @s varchar(max)
declare @sql nvarchar(max)
set @sql=''
set @s =''--必须初始化
SELECT @s += NAME +'+'+'''|''' +'+'FROM syscolumns WHERE ID=OBJECT_ID('testtb')
set @s = SUBSTRING(@s,0,LEN(@s))
set @s = SUBSTRING(@s,CHARINDEX('+',@s,0)+5,len(@s))
select @sql =N'select '+@s +' from testtb where id =2' --根据ID查询
exec (@sql)
drop table testtb
参考:
create table testtb (id int identity(1,1),A1 INT,A2 DATETIME,A3 varchar(30),A4 VARCHAR(30))
insert into testtb(A1,A2,A3)
select '1',GETDATE(),'c'
insert into testtb(A1,A2,A3)
select '11',GETDATE(),'cc'
declare @s varchar(max)='CAST(A1 AS varchar(30))+''|''+CAST(A2 AS varchar(30))+''|''+A3+''|'''--手动添加所有要的列(因为你最终要的是字符串所以其他类型的都可以先转为字符的)
declare @sql nvarchar(max)
set @sql=''
select @sql =N'select '+@s +' from testtb where id =2' --根据ID查询
exec (@sql)--动态执行
drop table testtb