declare @cols varchar(1000)
declare @sql varchar(8000)
set @cols = 'AA,DD'
select @sql = 'select * into #tmp from (select ' + @cols + ' from A union all select ' + @cols + ' from B) t'
exec( @sql )
declare @sql varchar(8000)
set @cols = 'AA,DD'
select @sql = 'select * into #tmp from (select ' + @cols + ' from A union all select ' + @cols + ' from B) t'
exec( @sql )
----------分离字符串-----------if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_split]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_split]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[序数表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [序数表]
GO--为了效率,所以要一个辅助表配合
select top 8000 id=identity(int,1,1) into 序数表
from syscolumns a,syscolumns b
alter table 序数表 add constraint pk_id_序数表 primary key(id)
go/*--字符串分函数 分拆指定分隔符的的字符串,返回结果表--邹建 2004.04(引用请保留此信息)--*//*--调用示例 select * from f_split('001|002|003|009|085','|')
--*/
create function f_split(
@str varchar(8000), --要分拆的字符串
@splitchar varchar(10) --分隔符
)returns table
as
return(
select re=substring(@str,id,charindex(@splitchar,@str+@splitchar,id)-id)
from 序数表
where id<=len(@str)+1 and charindex(@splitchar,@splitchar+@str,id)-id=0
)
go
(@column varchar(255))
as
declare @exec varchar(8000)
select @exec = 'select '+@column+' from A union all select ' + @cols + ' from B'
exec( @exec )
--测试数据
xxx 'AA,DD'
use pubs
go
if exists (select name from sysobjects
where name = 'my_pro' and type = 'p')
drop procedure my_pro
gocreate procedure my_pro
@cols varchar(1000)
as
create table #A(AA varchar(100),BB varchar(100),CC varchar(100),DD varchar(100),EE varchar(100))
create table #B(AA varchar(100),BB varchar(100),CC varchar(100),DD varchar(100),EE varchar(100))
insert into #A values('a','b','aa','bb','cc')
insert into #B values('aaa','asdfb','aaa','dbb','dcc')
declare @str varchar(8000)
set @str = 'select '+@cols+' from #A union all select '+@cols+' from #B'
--print @str
exec(@str)
drop table #A
drop table #B
go
exec my_pro 'AA,DD'
create table A(AA varchar(5),BB varchar(5),CC varchar(5),DD varchar(5),EE varchar(5))
create table B(AA varchar(5),BB varchar(5),CC varchar(5),DD varchar(5),EE varchar(5))insert into A select '甲','乙','丙','丁','戊'
insert into A select '己','艮','辛','壬','癸'
insert into B select '子','丑','寅','卯','辰'--声明变量
declare @collist varchar(20)
set @collist='AA,BB,'--构造动态语句
declare @sql varchar(8000)
set @sql='select '
while charindex(',',@collist)>0
begin
set @sql=@sql+left(@collist,charindex(',',@collist)-1)+'=a.'+left(@collist,charindex(',',@collist)-1)+'+b.'+left(@collist,charindex(',',@collist)-1)+','
set @collist=right(@collist,len(@collist)-charindex(',',@collist))
end
set @sql=left(@sql,len(@sql)-1)+' from A a,B b'exec (@sql)--删除表
drop table A,B--返回(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)AA BB
---------- ----------
甲子 乙丑
己子 艮丑
你的题目没给出A、B关联的条件,我默认用的全连接。
如果你还要根据条件连接,改下面这行即可
set @sql=left(@sql,len(@sql)-1)+' from A a,B b'
如:
set @sql=left(@sql,len(@sql)-1)+' from A a join B b on ……'
AA|BB|CC|DD|EE
有张表B,也是5列
AA|BB|CC|DD|EE
并且列名、类型,长度等相同
现在传入这个一个字符串"AA,DD,"字符串格式就是这样,固定了的。那么这个存储过程就把表A的AA列加上表B的AA列,表A的DD列加上表B的DD列。是分别对应行着加(比如A表的AA列的第一行加上B表AA列的第一行,第二行又加第二行)成为新的两列,并就用这两列创建一张临时表,把计算出来的值分别对应的插入这张临时表里。 最关键的是传入的列数是动态的,不一定只有两列,表A和表B也是动态的。 谢谢了
create table A(AA varchar(5),BB varchar(5),CC varchar(5),DD varchar(5),EE varchar(5))
create table B(AA varchar(5),BB varchar(5),CC varchar(5),DD varchar(5),EE varchar(5))insert into A select '甲','乙','丙','丁','戊'
insert into A select '己','艮','辛','壬','癸'
insert into B select '子','丑','寅','卯','辰'select identity(int,1,1)id,* into #A from A
select identity(int,1,1)id,* into #B from B
--声明变量
declare @collist varchar(20)
set @collist='AA,BB,'--构造动态语句
declare @sql varchar(8000)
set @sql='select '
while charindex(',',@collist)>0
begin
set @sql=@sql+left(@collist,charindex(',',@collist)-1)+'=a.'+left(@collist,charindex(',',@collist)-1)+'+b.'+left(@collist,charindex(',',@collist)-1)+','
set @collist=right(@collist,len(@collist)-charindex(',',@collist))
end
set @sql=left(@sql,len(@sql)-1)+' from #A a join #B b on a.id=b.id'exec (@sql)--删除表
drop table A,B,#A,#B
真的,谢谢你了