最好是
create procedure test
as
declare @sql varchar(8000)
set @sql ='select b.AcadYear,b.Id,c.Name'
select @sql =@sql+',sum(case SubName when '''+SubName+''''+' then SubValue end) ['+SubName+']'
from (select distinct SubName from MyTestTable where acadyear = '条件1') as a
select @sql=@sql+',sum(SubValue) as SumValue into NewTestTable from MyTestTable b inner join MyBasicInfo c'
select @sql=@sql+' on b.acadyear=c.acadyear and b.Id=c.Id where c.Name = '''+条件2+''''
select @sql=@sql+' group by b.AcadYear,b.Id,c.Name'
exec(@sql)
然后调用此存储过程。
痛苦ing........
2:
试试
SqlStr:= '...'将上面那一段放进去,但是将其中的每一个单引号用两个单引号来代替。
@tbname sysname
as
declare @sql varchar(8000)
set @sql='select * from '+@tbname
exec(@sql)
go
@Acadyear varchar(10),@TempTableName varchar(30)
as
declare @sql varchar(8000),@delsql varchar(100)
set @delsql= 'if exists (select name from sysobjects where me '+@TempTableName +') drop table '+@TempTableName
exec(@delsql)set @sql ='select b.AcadYear,b.Id,c.Name'
select @sql =@sql+',sum(case SubName when '''+SubName+''''+' then SubValue end) ['+SubName+']'
from (select distinct SubName from MyTestTable where acadyear = @AcadYear) as a
select @sql=@sql+',sum(SubValue) as SumValue into '+@TempTableName+' from MyTestTable b inner join MyBasicInfo c'
select @sql=@sql+' on b.acadyear=c.acadyear and b.Id=c.Id where c.Name = '''+@AcadYear+''''
select @sql=@sql+' group by b.AcadYear,b.Id,c.Name'
exec(@sql)
@Acadyear varchar(10),@TempTableName varchar(30)
as
declare @sql varchar(8000),@delsql varchar(100)
set @delsql= 'if exists (select name from sysobjects where name= '+@TempTableName +') drop table '+@TempTableName
exec(@delsql)set @sql ='select b.AcadYear,b.Id,c.Name'
select @sql =@sql+',sum(case SubName when '''+SubName+''''+' then SubValue end) ['+SubName+']'
from (select distinct SubName from MyTestTable where acadyear = @AcadYear) as a
select @sql=@sql+',sum(SubValue) as SumValue into '+@TempTableName+' from MyTestTable b inner join MyBasicInfo c'
select @sql=@sql+' on b.acadyear=c.acadyear and b.Id=c.Id where b.acadyear= '''+@AcadYear+''''
select @sql=@sql+' group by b.AcadYear,b.Id,c.Name'
exec(@sql)
@Acadyear varchar(10),
@TempTableName varchar(30)
as
declare @sql varchar(8000),@delsql varchar(100)
set @delsql= 'if exists (select name from sysobjects where name='''+@TempTableName +''') drop table '+@TempTableName --这错了.
exec(@delsql)set @sql ='select b.AcadYear,b.Id,c.Name'
select @sql =@sql+',sum(case SubName when '''+SubName+''''+' then SubValue end) ['+SubName+']'
from (select distinct SubName from MyTestTable where acadyear = @AcadYear) as a
select @sql=@sql+',sum(SubValue) as SumValue into '+@TempTableName+' from MyTestTable b inner join MyBasicInfo c'
select @sql=@sql+' on b.acadyear=c.acadyear and b.Id=c.Id where b.acadyear= '''+@AcadYear+''''
select @sql=@sql+' group by b.AcadYear,b.Id,c.Name'
exec(@sql)
go