SQL Server语言 - 在 Excel 中使用动态 SQL 存储过程保存时出现问题

Question

存储过程：

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>0 THEN ',j1001' ELSE '' END +                    
  CASE WHEN j1002>0 THEN ',j1002' ELSE '' END +        
  CASE WHEN j9999>0 THEN ',j9999' ELSE '' END               
  FROM machinejob_report  
  WHERE  machinename = 'Grand Total:'  
  IF(LEN(@dynsql) > 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

上述过程的输出：

  machinename     j1001     j1002     j9999
   EC              270      NULL       200
   RUN             NULL      420       300
   Grand Total:    270       420       500  

将其

保存在 xls 中的另一个过程：

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+' >> "'+@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'   

当我执行上述过程时..它没有显示任何错误..但是只有一列保存在.csv/.xls文件中...

.csv文件中的输出：

  EC   

请帮助我解决这个问题...

溶液：

我在上面的过程中在动态选择语句旁边创建了另一个动态表，并将结果集插入到该表中，

 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)

在将它保存在XLS中的过程中再次使用了新创建的表：

 --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   

现在我能够获取 Excel 工作表中的所有行

Answer 1

您可能会

发现在SQL Server Integration Services （SSIS）中执行此工作更容易。它是专门为处理这种事情而设计的。

如果需要生成两个文件，然后将它们附加到自动电子邮件，则可以在 SSIS 中创建这两个文件，并将整个文件包装在运行 SSIS 作业然后发送电子邮件的 SQL 代理作业中。

希望这对你有意义。

http://msdn.microsoft.com/en-us/library/ms169917.aspx