title: Adding column dynamically in Temp table in Ms Sql Server?
body: <p>In Sql server, i write a procedure and i use one tem table and a cursor and dynamically add one column to that temporary table but it is giving erro :</p>
  
  <pre><code>(10 row(s) affected)
  Msg 213, Level 16, State 1, Procedure USP_F_Roll_AllIndia_Report, Line 27
  Column name or number of supplied values does not match table definition.
  </code></pre>
  
  <p>This is my proc :</p>
  
  <pre><code>alter procedure USP_F_Roll_AllIndia_Report       
  (@segcode int,@rollplanyear int)
  as
   begin
  declare @cfcode varchar(10)
  declare @cfname varchar(30)
  declare @SQl nvarchar(max)  
  
  create table #TEP (productcode varchar(10) collate database_default,proddesc varchar(100))
  
  declare db_cursor cursor for   
  
  select distinct canfm.CFCode, SUBSTRING (CANFM.CFName,4,5)as CFName   from Tbl_F_CandF_M CANFM left outer join   Tbl_F_Org_CandF_T CT  on  CANFM.CFCode = ct.CFCode where  CANFM .status =1 and ct.Status =1 order by canfm.cfcode   
  
  open db_cursor 
  
  fetch next from db_cursor into @cfcode, @cfname
  
  while @@FETCH_STATUS =0
    begin
  
      set @SQL ='alter table #TEP add '+@cfname+' float'  
      exec sp_executesql @Sql
      --exec  ( @Sql)
  
  
      insert into #TEP 
  
      select pd.productcode,PM.productdesc,convert(varchar,sum(isnull(AmendedQty,isnull(Quantity,0))))as quantity    from Tbl_F_Roll_PlanDetails_T pd left outer join Tbl_F_ProductMaster_M PM on
      pd.ProductCode =pm.ProductCode left outer join Tbl_F_CandF_M CANDF on pd.CandFLocation =CANDF.CFCode    where pd. RollPlanYear =@rollplanyear and pd.CandFLocation =@cfcode  and  pd.ProductCode in (
      select ProductCode  from   Tbl_F_Segment_Product_t where SegCode  =@segcode )    group by pd.ProductCode,pm.ProductDesc  
  
     fetch next from db_cursor into @cfcode, @cfname
   end              
       close db_cursor              
       deallocate db_cursor                                             
       select * from #TEP               
   end  
  </code></pre>