1 use EntDataCenter  2 go  3 SET ANSI_NULLS ON  4 GO  5 SET QUOTED_IDENTIFIER ON  6 GO  7 -- =============================================  8 -- Author:        <Author,,Name>  9 -- Create date: <Create Date,,> 10 -- Description:    <Description,,> 11 -- ============================================= 12 --exec dbo.ent_all_DataToExcel 13 create PROCEDURE  ent_all_DataToExcel 14  15 AS 16 BEGIN 17     -- SET NOCOUNT ON added to prevent extra result sets from 18     -- interfering with SELECT statements. 19     SET NOCOUNT off; 20     DECLARE @str2name varchar(8000);--二级国代分类名称 21     DECLARE @str2code varchar(8000);--二级国代分类代码 22     DECLARE @str1name varchar(8000);--一级国代分类名称 23     DECLARE @str varchar(8000); 24     DECLARE @str1 varchar(8000); 25     DECLARE @server varchar(100);--服务器 26     DECLARE @uname varchar(100);--用户名 27     DECLARE @pwd varchar(100);--密码 28      29     DECLARE @IsExist bit; 30     select @str2name=‘‘; 31     select @str2code=‘‘; 32     select @str1name=‘‘; 33     select @str=‘‘; 34     select @str1=‘‘; 35     set @server=‘192.168.1.7‘; 36     set @uname=‘dev_db‘; 37     set @pwd=‘dev_db‘; 38     set @IsExist=0; 39      40     ---判断文件夹是否存在，若不存在则创建文件夹---- 41     begin 42         CREATE TABLE #tmp ([File Exists] BIT, [File is a Directory] BIT, [Parent Directory Exists] BIT) 43         INSERT INTO #tmp ([File Exists], [File is a Directory], [Parent Directory Exists]) 44         EXEC master.dbo.xp_fileexist ‘c:\project‘; 45         SELECT @IsExist=[File is a Directory] FROM #tmp; 46         if(@IsExist=0) 47         begin 48           ExEc xp_cmdshell ‘mkdir c:\project‘; 49         end 50         drop table #tmp 51     end 52      53     DECLARE contact_cursor CURSOR FOR (SELECT   a.国代分类名称 as 二级国代分类名称,a.国代分类代码 as 二级国代分类代码,b.国代分类名称 as 一级国代分类名称 54                                         FROM [EntDataCenter].[dbo].[doc_class_nation_code]  a 55                                         inner join   [EntDataCenter].[dbo].[doc_class_nation_code]   b 56                                         on a.PCode=b.国代分类代码  where a.Level=1) 57     OPEN contact_cursor 58             FETCH NEXT FROM contact_cursor 59             INTO @str2name,@str2code,@str1name 60             WHILE @@FETCH_STATUS = 0 61             BEGIN         62                          begin 63                         set @str=‘select   a.[企业名称],a.[所在省份],a.[所在城市],‘ ; 64                         set @str=@str+‘(case b.year when 2005  then b.[total_assets] else 0 end) as [2005资产],‘; 65                         set @str=@str+‘(case b.year when 2006  then b.[total_assets] else 0 end) as [2006资产],‘; 66                         set @str=@str+‘(case b.year when 2007  then b.[total_assets] else 0 end) as [2007资产],‘; 67                         set @str=@str+‘(case b.year when 2008  then b.[total_assets] else 0 end) as [2008资产],‘; 68                         set @str=@str+‘(case b.year when 2009  then b.[total_assets] else 0 end) as [2009资产],‘; 69                         set @str=@str+‘(case b.year when 2010  then b.[total_assets] else 0 end) as [2010资产],‘; 70                         set @str=@str+‘(case b.year when 2011  then b.[total_assets] else 0 end) as [2011资产],‘; 71                         set @str=@str+‘(case b.year when 2012  then b.[total_assets] else 0 end) as [2012资产],‘; 72                         set @str=@str+‘(case b.year when 2005  then b.total_current_assets else 0 end) as [2005流动资产],‘; 73                         set @str=@str+‘(case b.year when 2006  then b.total_current_assets else 0 end) as [2006流动资产],‘; 74                         set @str=@str+‘(case b.year when 2007  then b.total_current_assets else 0 end) as [2007流动资产],‘; 75                         set @str=@str+‘(case b.year when 2008  then b.total_current_assets else 0 end) as [2008流动资产],‘; 76                         set @str=@str+‘(case b.year when 2009  then b.total_current_assets else 0 end) as [2009流动资产],‘; 77                         set @str=@str+‘(case b.year when 2010  then b.total_current_assets else 0 end) as [2010流动资产],‘; 78                         set @str=@str+‘(case b.year when 2011  then b.total_current_assets else 0 end) as [2011流动资产],‘; 79                         set @str=@str+‘(case b.year when 2012  then b.total_current_assets else 0 end) as [2012流动资产],‘; 80                         set @str=@str+‘(case b.year when 2005  then b.total_liabilities else 0 end) as [2005负债],‘; 81                         set @str=@str+‘(case b.year when 2006  then b.total_liabilities else 0 end) as [2006负债],‘; 82                         set @str=@str+‘(case b.year when 2007  then b.total_liabilities else 0 end) as [2007负债],‘; 83                         set @str=@str+‘(case b.year when 2008  then b.total_liabilities else 0 end) as [2008负债],‘; 84                         set @str=@str+‘(case b.year when 2009  then b.total_liabilities else 0 end) as [2009负债],‘; 85                         set @str=@str+‘(case b.year when 2010  then b.total_liabilities else 0 end) as [2010负债],‘; 86                         set @str=@str+‘(case b.year when 2011  then b.total_liabilities else 0 end) as [2011负债],‘; 87                         set @str=@str+‘(case b.year when 2012  then b.total_liabilities else 0 end) as [2012负债],‘; 88                         set @str=@str+‘(case b.year when 2005  then b.operating_income else 0 end) as [2005收入],‘; 89                         set @str=@str+‘(case b.year when 2006  then b.operating_income else 0 end) as [2006收入],‘; 90                         set @str=@str+‘(case b.year when 2007  then b.operating_income else 0 end) as [2007收入],‘; 91                         set @str=@str+‘(case b.year when 2008  then b.operating_income else 0 end) as [2008收入],‘; 92                         set @str=@str+‘(case b.year when 2009  then b.operating_income else 0 end) as [2009收入],‘; 93                         set @str=@str+‘(case b.year when 2010  then b.operating_income else 0 end) as [2010收入],‘; 94                         set @str=@str+‘(case b.year when 2011  then b.operating_income else 0 end) as [2011收入],‘; 95                         set @str=@str+‘(case b.year when 2012  then b.operating_income else 0 end) as [2012收入],‘; 96                         set @str=@str+‘(case b.year when 2005  then b.operating_costs else 0 end) as [2005成本],‘; 97                         set @str=@str+‘(case b.year when 2006  then b.operating_costs else 0 end) as [2006成本],‘; 98                         set @str=@str+‘(case b.year when 2007  then b.operating_costs else 0 end) as [2007成本],‘; 99                         set @str=@str+‘(case b.year when 2008  then b.operating_costs else 0 end) as [2008成本],‘;100                         set @str=@str+‘(case b.year when 2009  then b.operating_costs else 0 end) as [2009成本],‘;101                         set @str=@str+‘(case b.year when 2010  then b.operating_costs else 0 end) as [2010成本],‘;102                         set @str=@str+‘(case b.year when 2011  then b.operating_costs else 0 end) as [2011成本],‘;103                         set @str=@str+‘(case b.year when 2012  then b.operating_costs else 0 end) as [2012成本],‘;104                         set @str=@str+‘(case b.year when 2005  then b.total_profit else 0 end) as [2005利润总额],‘;105                         set @str=@str+‘(case b.year when 2006  then b.total_profit else 0 end) as [2006利润总额],‘;106                         set @str=@str+‘(case b.year when 2007  then b.total_profit else 0 end) as [2007利润总额],‘;107                         set @str=@str+‘(case b.year when 2008  then b.total_profit else 0 end) as [2008利润总额],‘;108                         set @str=@str+‘(case b.year when 2009  then b.total_profit else 0 end) as [2009利润总额],‘;109                         set @str=@str+‘(case b.year when 2010  then b.total_profit else 0 end) as [2010利润总额],‘;110                         set @str=@str+‘(case b.year when 2011  then b.total_profit else 0 end) as [2011利润总额],‘;111                         set @str=@str+‘(case b.year when 2012  then b.total_profit else 0 end) as [2012利润总额]‘;112                         set @str=@str+‘from [dbo].[ent_all] a ‘;113                         set @str=@str+‘left join [dbo].[Ent_All_Finance] b on a.ent_id = b.ent_id  where a.国代分类代码 like ‘‘‘+@str2code+‘%‘‘‘;114                         115                         --set  @str=‘select * from [EntDataCenter].[dbo].[doc_class_nation_code] where Level=1‘;116                         set @str1=‘bcp "‘+@str+‘" queryout C:\project\‘+@str1name+‘-‘+@str2name+‘.xls -c -S‘+@server+‘ -U‘+@uname+‘ -P‘+@pwd+‘‘;117                         end 118                         --print @str1;119                         EXEC master..xp_cmdshell @str1120                        121                        122               FETCH NEXT FROM contact_cursor123               INTO @str2name,@str2code,@str1name124             END125             CLOSE contact_cursor126     DEALLOCATE contact_cursor127  128 END129 GO