SQL 存储过程动态生成列

USE [BM]
GO
/****** Object:  StoredProcedure [dbo].[zsp_weekneedata]    Script Date: 2015-12-19 15:36:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 ALTER procedure [dbo].[zsp_weekneedata] @startdate char(8),@enddate char(8),@s char(1),@count int
    --开始日期,结束日期,是否显示仓库,指定周期即循环数
   as
    declare @tempdate char(8),@sql varchar(8000),@SQL1 VARCHAR(8000)--,@enddate char(8),@startdate char(8)      varchar(8000)最长字符8000
    SElect @SQL1=' select TB003 ',@tempdate= @startdate,@sql=''
    while (@enddate>=@tempdate)
    BEGIN
        set @sql=@sql+',isnull(sum(case when  TA009>='''+@tempdate + ''' and TA009<CONVERT( char(8), dateadd(dd,7,'''+@tempdate+'''),112) then isnull(TB004-TB005,0) end),0) AS WEEK'+CONVERT(CHAR(8),@count)
set @count= @count+1
set @tempdate=CONVERT( char(8), dateadd(dd,7,@tempdate),112)
end
     set @SQL1=@SQL1+@sql+',sum(TB004-TB005) AS 总需领量 FROM MOCTA LEFT JOIN MOCTB ON TA001=TB001 AND TA002=TB002 WHERE TA011 NOT IN (''y'',''Y'') and TB004-TB005>=0 and TA009>= '''+@startdate+''' and TA009<'''+@enddate+''' group by TB003 ' --


    if(@s='0')--值为0省略仓库,值为1添加仓库列
  exec( 'select C.MB002 as 品名,C.MB003 as 规格,B.SQTY as 库存数, A.*,B.SQTY-A.总需领量 AS 结余数  from ('+@SQL1 +') A right join (select A.MC001,SUM(A.MC007) AS SQTY from INVMC A left join CMSMC B ON A.MC002=B.MC001  
WHERE B.MC004=''1'' and B.MC005=''Y'' 
group by A.MC001) B ON  B.MC001=A.TB003 LEFT JOIN INVMB C ON MB001=A.TB003 WHERE C.MB005=''02'' order by A.TB003' )
    else
  exec( 'select C.MB002 as 品名,C.MB003 as 规格,B.SQTY as 库存数,A.*,D.不良品仓,D.借出仓,B.SQTY+D.不良品仓+D.借出仓-A.总需领量 AS 结余数  from ('+@SQL1 +') A LEFT join (select A.MC001,SUM(A.MC007) AS SQTY from INVMC A left join CMSMC B ON A.MC002=B.MC001  
WHERE B.MC004=''1'' and B.MC005=''Y''
group by A.MC001) B ON  B.MC001=A.TB003 LEFT JOIN INVMB C ON MB001=A.TB003 left join (SELECT MC001,SUM(CASE WHEN MC002=''CA01'' THEN MC007 ELSE 0 END) AS ''不良品仓'' ,
SUM(CASE WHEN MC002=''CA02'' THEN MC007 ELSE 0 END) AS ''借出仓''  FROM INVMC  WHERE MC002 IN(''CA02'',''CA01'') group by MC001 ) D ON D.MC001=A.TB003  WHERE C.MB005=''02'' ORDER BY A.TB003')