CREATE PROCEDURE Product_Balance
AS
select id,ProductName as '名称',ProductInfo as '介绍' into #templs from table3
declare @s1 varchar(8000),
@s2 varchar(8000),
@s3 varchar(8000),
@s5 varchar(8000),
@i varchar(10)
select @s1='',@s2='',@s3='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'=''ID='''''+name+''''''''
,@s3=@s3+'
select @'+@i+'=@'+@i+'+'',[''+cast(ID as varchar)+'']=''''''+cast(isnull('+name+','''') as varchar)+'''''''' from #templs '
,@s5=@s5+'+'' union all select ''+@'+@i
,@i=cast(@i as int)+1
from Tempdb..syscolumns
where object_id('Tempdb..#templs')=id and name<>'ID'
order by colid
CREATE PROCEDURE Product_Balance
AS
select id,ProductName as '名称',ProductInfo as '介绍' into #templs from table3
declare @s1 varchar(8000),
@s2 varchar(8000),
@s3 varchar(8000),
@s5 varchar(8000),
@i varchar(10)
select @s1='',@s2='',@s3='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'=''ID='''''+name+''''''''
,@s3=@s3+'
select @'+@i+'=@'+@i+'+'',[''+cast(ID as varchar)+'']=''''''+cast(isnull('+name+','''') as varchar)+'''''''' from #templs '
,@s5=@s5+'+'' union all select ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('#templs')=id and name<>'ID'
order by colid
--测试数据
create table 表([11] varchar(2),[22] int,[33] int,[44] int,[55] int)
insert 表 select 'aa',1,2,3,6
union all select 'bb',0,1,3,5
union all select 'cc',1,2,3,6
union all select 'dd',1,2,3,6
union all select 'ee',1,2,3,6
go
--查询处理
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'='''''
,@s3=@s3+'
select @'+@i+'=@'+@i+'+'',[''+[11]+'']=''+cast(['+name+'] as varchar) from 表'
,@s4=@s4+',@'+@i+'=''select ''+substring(@'+@i+',2,8000)'
,@s5=@s5+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('表')=id and colid<>1
declare @s1 varchar(8000),
@s2 varchar(8000),
@s3 varchar(8000),
@s5 varchar(8000),
@i varchar(10)
select @s1='',@s2='',@s3='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'=''ID='''''+name+''''''''
,@s3=@s3+'
select @'+@i+'=@'+@i+'+'',[''+cast(ID as varchar)+'']=''''''+cast(['+name+'] as varchar)+'''''''' from test'
,@s5=@s5+'+'' union all select ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('test')=id and name<>'ID'
order by colid