不固定列的报表存储过程

-- ***********************************--
--
-- 项目名称：网络端口报名
-- 功能描述：生成报名总表报表
-- 项目开发：Me

-- 时    间：2013年2月20日
-- exec p_ApplyInfoCreate 4
--
-- ***********************************--
ALTER proc [dbo].[p_ApplyInfoCreate]
@StageID   Int
as
 Set NOCOUNT ON
 --获取最多端口个数为列数
 Declare @ColumnCount Int
 Set @ColumnCount=0
 Select  @ColumnCount=Max(A.Countx)
 From (Select Count(1) Countx From dbo.t_PortApply Where StageID=@StageID and IsDelete=0 Group By OrgCode5,StaffCode) A
 
 --添加端口基本信息
 Declare @ColumnStr Nvarchar(Max)
 Set @ColumnStr=''
    While @ColumnCount>0
 Begin
  Set @ColumnStr=',网络渠道'+Cast(@ColumnCount As Varchar(10))+' Nvarchar(128),'+
        '版本套餐'+Cast(@ColumnCount As Varchar(10))+' Nvarchar(128),'+
        '端口单价'+Cast(@ColumnCount As Varchar(10))+' Nvarchar(128),'+
        '开通人手机号'+Cast(@ColumnCount As Varchar(10))+' Nvarchar(128),'+
        '端口用户名'+Cast(@ColumnCount As Varchar(10))+' Nvarchar(128)'+
         +@ColumnStr
  Set @ColumnCount=@ColumnCount-1
 End

    Declare @SqlStr Nvarchar(Max)
 Set @SqlStr='
 --定义表
 If Exists (Select 1 from dbo.sysobjects where ID = object_id(N''[dbo].[t_ApplyTable'+cast(@StageID as varchar(10))+']'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
  Drop Table t_ApplyTable'+cast(@StageID as varchar(10))+'
 Create Table t_ApplyTable'+Cast(@StageID As Varchar(10))+' (
        序号 Int Identity(1,1),
        事业部 Nvarchar(128),
        战区 Nvarchar(128),
        片区 Nvarchar(128),
        分行组别 Nvarchar(128),
        姓名 Nvarchar(128),
        工号 Nvarchar(128)'+@ColumnStr+')

  --插入基本信息
  Insert Into t_ApplyTable'+Cast(@StageID As Varchar(10))+'(事业部,战区,片区,分行组别,姓名,工号)
  Select  Distinct
    OrgName1,
    OrgName2,
    OrgName3,
    OrgName5,
    StaffName,
    StaffCode
  From t_PortApply
  Where StageID='+Cast(@StageID As Varchar(10))+'
    And IsDelete=0
  Order By OrgName1,OrgName2,OrgName3,OrgName5
 '
 exec sp_executesql @SqlStr

    --插入端口信息组串
 Declare @UpdateStr Nvarchar(Max)
 Set @UpdateStr=''
    Declare @ApplyTable Table(ID Int Identity(1,1),StaffCode Nvarchar(128))
 Insert Into @ApplyTable(StaffCode) Select Distinct StaffCode From t_PortApply WhereStageID=@StageID And IsDelete=0
 Declare @RowCount Int
 Select @RowCount=count(1) From @ApplyTable
 While @RowCount>0
 Begin
     Declare @StaffCode Nvarchar(128)
  Select @StaffCode=StaffCode From @ApplyTable Where ID=@RowCount
  --每个人报的产品
  Declare @tmpTable Table(ID Int,网络渠道 Nvarchar(128),版本套餐 Nvarchar(128),端口单价 Nvarchar(128),开通人手机号 Nvarchar(128),端口用户名 Nvarchar(128))
  Insert Into @tmpTable(ID,网络渠道,版本套餐,端口单价,开通人手机号,端口用户名)
        Select ROW_NUMBER()Over(order by ProductName),CanalName,ProductName,RealPrice,PhoneNum,PortName From t_PortApply WhereStaffCode=@StaffCode AndStageID=@StageID And IsDelete=0
  
  --修改端口信息组串
  Declare @PortCount Int
  Select @PortCount=Count(1) From @tmpTable
  While @PortCount>0
  Begin
   Declare @CanalName Nvarchar(128),@ProductName Nvarchar(128),@PortPrice Nvarchar(128),@PortName Nvarchar(128),@PhoneNum Nvarchar(128)
   Select  @CanalName=网络渠道,
     @ProductName=版本套餐,
     @PortPrice=端口单价,
     @PhoneNum=开通人手机号,
     @PortName=端口用户名
   From @tmpTable
   Where   ID=@PortCount
   Set @UpdateStr=' Update t_ApplyTable'+Cast(@StageID As Varchar(10))+' Set 网络渠道'+Cast(@PortCount As Nvarchar(10))+'='''+@CanalName+''',
                  版本套餐'+Cast(@PortCount As Nvarchar(10))+'='''+@ProductName+''',
                  端口单价'+Cast(@PortCount As Nvarchar(10))+'='''+@PortPrice+''',
                  开通人手机号'+Cast(@PortCount As Nvarchar(10))+'='''+@PhoneNum+''',
                  端口用户名'+Cast(@PortCount As Nvarchar(10))+'='''+@PortName+'''
        Where 工号='''+@StaffCode+''''
   exec sp_executesql @UpdateStr
   Set @PortCount=@PortCount-1
  End
  Delete From @tmpTable
  Set @RowCount=@RowCount-1
 End
 Declare @SqlStr1 Nvarchar(Max)
 Set @SqlStr1='Select * From t_ApplyTable'+cast(@StageID as varchar(10))+''
    exec sp_executesql @SqlStr1
 SET NOCOUNT OFF