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 @str1 120 121 122 FETCH NEXT FROM contact_cursor 123 INTO @str2name,@str2code,@str1name 124 END 125 CLOSE contact_cursor 126 DEALLOCATE contact_cursor 127 128 END 129 GO