title: problem in saving in excel using sql stored procedure with dynamic sql
body: <p>stored procedure :</p>
  
  <pre><code>alter  PROCEDURE usp_dailyjobwisereport_dayshift            
  as             
  /*                
  exec usp_dailyjobwisereport_dayshift          
  */                
  SET NOCOUNT ON                
  
  begin              
  
  delete from machinejob_report;
  
   insert into  machinejob_report   
      select coalesce(cast(description_name as varchar(28)), 'Grand Total:') as descriptionname, 
      sum(case when vfrm.job_id = '1001' then DateDiff(mi, 0, total_time)  end) as  CRATE_SMALL ,       
      sum(case when vfrm.job_id = '1002' then DateDiff(mi, 0, total_time)  end) as  CRATE_MEDIUM ,                  
      sum(case when vfrm.job_id = '9999' then DateDiff(mi, 0, total_time)  end) as  NO_SCHEDULE
      from    ven_fullreportmaster vfrm
               INNER JOIN ven_descriptionmaster VDM ON VDM.description_id = vfrm.description_id
            -- inner join ven_machinemaster vm on  vm.machine_id = vfrm..machine_id
                          where  vfrm.entry_date = convert(varchar, getdate()-9, 105)  
                        and  vfrm.shift_id =1 
                        and vfrm.description_id in (1,3,5)
                        and vfrm.is_task_completed ='Y' 
            group by   description_name  with rollup
  
   if (1=2) 
   begin
   select system_type_id, user_type_id from sys.types
  end
  
   DECLARE @dynsql NVARCHAR(MAX)  
  SELECT @dynsql = 
    CASE WHEN j1001&gt;0 THEN ',j1001' ELSE '' END +                    
    CASE WHEN j1002&gt;0 THEN ',j1002' ELSE '' END +        
    CASE WHEN j9999&gt;0 THEN ',j9999' ELSE '' END               
    FROM machinejob_report  
    WHERE  machinename = 'Grand Total:'  
    IF(LEN(@dynsql) &gt; 0)     
   BEGIN  
     /*
       DECLARE @f NVARCHAR(MAX) 
      SELECT @f = COALESCE(@dynsql + ', ', '') + field FROM  ven_machinejob_report_temp  
      SET @dynsql = 'SELECT machinename ' + @f + ' FROM machinejob_report' 
    */
   SET @dynsql = STUFF(@dynsql,1,1,'SELECT machinename, ') + ' FROM machinejob_report'     
   EXEC(@dynsql)    
   END 
  
  end                  
  
  SET NOCOUNT OFF
  </code></pre>
  
  <p>Output of the above procedure:</p>
  
  <pre><code>  machinename     j1001     j1002     j9999
     EC              270      NULL       200
     RUN             NULL      420       300
     Grand Total:    270       420       500  
  </code></pre>
  
  <p>Another procedure for saving it in xls:</p>
  
  <pre><code>alter procedure usp_dailyjobwisereport_dayshift_excel   
  (    
    @db_name varchar(100), @schm_name varchar(100), @table_name varchar(100),@file_name  varchar(100)    
   )    
   as    
  
   --Generate column names as a recordset    
   declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)    
  
  set @columns = '''machinename'' as machinename,''1001'' as job1001, ''1002'' as job1002 , ''9999'' as job9999'; 
  
      --Create a dummy file to have actual data    
     select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.csv'    
  
    --Generate column names in the passed EXCEL file    
    set @sql='bcp " select * from (select '+@columns+') as t" queryout   c:\test.csv  -c -t,  -T -S ' + @@servername    
    exec master..xp_cmdshell @sql   
  
      --Generate data in the dummy file    
      set @sql='bcp "exec Test..usp_dailyjobwisereport_dayshift " queryout   c:\data_file.csv  -c -t,  -T -S ' + @@servername    
     exec master..xp_cmdshell @sql    
  
   --Copy dummy file to passed EXCEL file    
     set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' &gt;&gt; "'+@file_name+'"'''    
    exec(@sql)    
  
  --Delete dummy file     
  --set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''    
  --exec(@sql)    
  
  --EXEC usp_dailyjobwisereport_dayshift_excel Test','[dbo]', 'usp_dailyjobwisereport_dayshift','c:\test.csv'   
  </code></pre>
  
  <p>When I execute the above procedure.. it's not showing any error.. but only one column is saved in the .csv /.xls file...</p>
  
  <p>Output in the .csv file:</p>
  
  <pre><code>  EC   
  </code></pre>
  
  <p>Please help me out to solve this issue...</p>
  
  <h2>Solution:</h2>
  
  <p>i have created another dynamic table in the above procedure next to dynamic select statement  and inserted the result set into that table and </p>
  
  <pre><code> set @f = STUFF(@dyncreate,1,6,'create table ven_machinejob_report_temp( descriptionname nvarchar(111),' )  + '   int )'
    exec(@f)
    insert into ven_machinejob_report_temp   
    EXEC(@dynsql)
  </code></pre>
  
  <p>again used that newly created table in the procedure for saving it in xls:</p>
  
  <pre><code> --Generate data in the dummy file    
   set @sql='bcp " select * from Test..ven_machinejob_report_temp " queryout   c:\data_file.csv  -c -t,  -T -S ' + @@servername    
   exec master..xp_cmdshell @sql   
  </code></pre>
  
  <p>and now i am able to get all the rows in the excel sheet</p>