如何将存储过程的输出结果自动存成excel文件?

[好记忆不如烂笔头abc]

比如我有个存储过程叫sp_excel,执行exec sp_excel将输出查询结果,如何将这个结果自动存储excel文件呢?

[好记忆不如烂笔头abc]

yjdn(文刀无尽) 和zjcxc(邹建) 两位老大，能否帮忙呢？十分感谢！

[yjdn]

http://blog.csdn.net/zjcxc/archive/2003/12/29/20084.aspx
--这里有邹建写的，导出到Excel的存储过程


你可以这样，想简单点的话，就把你的结果放到一个表里，然后再把这个表的数据导出到Excel

[xyxfly]

:)

[好记忆不如烂笔头abc]

不适用啊,我的存储过程有一些参数传入的.

比如我执行exec sp_excel 123,'2005-09-12','2006-09-12','a'
这样会出来查询结果.
我希望能将查询结果自动存成excel文件,请高手帮忙,十分感谢!

[xyxfly]

这样试试?
在存储过程里把你的结果保存到一个表里,然后把表导入EXCEL

[yjdn]

你有参数有什么关系？

我已经说过了，你把查询结果插入到一个表中，

然后再用那个存储过程导出

[yjdn]

就是说，你不是返回一个查询吗？

select * into 表名 form ...



就是在你的查询里加 into 表名

然后再加一句 exec 导出存储过程，那不就OK？

后面再Drop Table 表名，


多用户时，用临时表

[zjcxc]

我的导出存储过程的第2个, 接受的参数是查询语句, 你先把查询语句准备好,再传过去就行了.

[好记忆不如烂笔头abc]

exec sp_excel 123,'2005-09-12','2006-09-12','a'
================================================
存储过程如下，实在不知道改怎么改，多谢了！
========================
CREATE PROCEDURE sp_excel
@id bigint,
@start datetime,
@finish datetime,
@px char(2)
AS
declare @t1 float
declare @t2 float
if @px='a' or @px='b'
begin
if @start<>'' and @finish<>''
begin
select @t1=sum(c1),@t2=sum(c2) from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id and vdate>=@start and vdate<=@finish group by ziduan) as temp
select convert(nvarchar(4),year(vdate))+'-'+convert(nvarchar(2),month(vdate))+'-'+convert(nvarchar(2),day(vdate)) as '日期',convert(nvarchar(10),ziduan) as '字段',sum(cishu1) as '次数1',convert(nvarchar(5),round(sum(cishu1)*100/@t1,2))+'%' as '次数1比例',sum(cishu2) as '次数2',convert(nvarchar(5),round(sum(cishu2)*100/@t2,2))+'%' as '次数2比例' from test_table where
id=@id and vdate>=@start and vdate<=@finish group by vdate,ziduan
union
select '合计','',sum(c1),'100%',sum(c2),'100%' from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id and vdate>=@start and vdate<=@finish group by ziduan) as temp order by 1,3 desc,5 desc
end

if @start='' and @finish<>''
begin
select @t1=sum(c1),@t2=sum(c2) from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id and vdate<=@finish group by ziduan) as temp
select convert(nvarchar(4),year(vdate))+'-'+convert(nvarchar(2),month(vdate))+'-'+convert(nvarchar(2),day(vdate)) as '日期',convert(nvarchar(10),ziduan) as '字段',sum(cishu1) as '次数1',convert(nvarchar(5),round(sum(cishu1)*100/@t1,2))+'%' as '次数1比例',sum(cishu2) as '次数2',convert(nvarchar(5),round(sum(cishu2)*100/@t2,2))+'%' as '次数2比例' from test_table where
id=@id and vdate<=@finish group by vdate,ziduan
union
select '合计','',sum(c1),'100%',sum(c2),'100%' from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id and vdate<=@finish group by ziduan) as temp order by 1,3 desc,5 desc
end

if @start<>'' and @finish=''
begin
select @t1=sum(c1),@t2=sum(c2) from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id and vdate>=@start group by ziduan) as temp
select convert(nvarchar(4),year(vdate))+'-'+convert(nvarchar(2),month(vdate))+'-'+convert(nvarchar(2),day(vdate)) as '日期',convert(nvarchar(10),ziduan) as '字段',sum(cishu1) as '次数1',convert(nvarchar(5),round(sum(cishu1)*100/@t1,2))+'%' as '次数1比例',sum(cishu2) as '次数2',convert(nvarchar(5),round(sum(cishu2)*100/@t2,2))+'%' as '次数2比例' from test_table where
id=@id and vdate>=@start group by vdate,ziduan
union
select '合计','',sum(c1),'100%',sum(c2),'100%' from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id and vdate>=@start group by ziduan) as temp order by 1,3 desc,5 desc
end

if @start='' and @finish=''
begin
select @t1=sum(c1),@t2=sum(c2) from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id group by ziduan) as temp
select convert(nvarchar(4),year(vdate))+'-'+convert(nvarchar(2),month(vdate))+'-'+convert(nvarchar(2),day(vdate)) as '日期',convert(nvarchar(10),ziduan) as '字段',sum(cishu1) as '次数1',convert(nvarchar(5),round(sum(cishu1)*100/@t1,2))+'%' as '次数1比例',sum(cishu2) as '次数2',convert(nvarchar(5),round(sum(cishu2)*100/@t2,2))+'%' as '次数2比例' from test_table where
id=@id group by vdate,ziduan
union
select '合计','',sum(c1),'100%',sum(c2),'100%' from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id group by ziduan) as temp order by 1,3 desc,5 desc
end
end

--px=c,d
if @px='c' or @px='d'
begin
if @start<>'' and @finish<>''
begin
select @t1=sum(c1),@t2=sum(c2) from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id and vdate>=@start and vdate<=@finish group by ziduan) as temp
select convert(nvarchar(4),year(vdate))+'年'+convert(nvarchar(2),month(vdate))+'月' as '月份',convert(nvarchar(10),ziduan) as '字段',sum(cishu1) as '次数1',convert(nvarchar(5),round(sum(cishu1)*100/@t1,2))+'%' as '次数1比例',sum(cishu2) as '次数2',convert(nvarchar(5),round(sum(cishu2)*100/@t2,2))+'%' as '次数2比例' from test_table where
id=@id and vdate>=@start and vdate<=@finish group by convert(nvarchar(4),year(vdate))+'年'+convert(nvarchar(2),month(vdate))+'月',ziduan
union
select '合计','',sum(c1),'100%',sum(c2),'100%' from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id and vdate>=@start and vdate<=@finish group by ziduan) as temp order by 1,3 desc,5 desc
end

if @start='' and @finish<>''
begin
select @t1=sum(c1),@t2=sum(c2) from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id and vdate<=@finish group by ziduan) as temp
select convert(nvarchar(4),year(vdate))+'年'+convert(nvarchar(2),month(vdate))+'月' as '月份',convert(nvarchar(10),ziduan) as '字段',sum(cishu1) as '次数1',convert(nvarchar(5),round(sum(cishu1)*100/@t1,2))+'%' as '次数1比例',sum(cishu2) as '次数2',convert(nvarchar(5),round(sum(cishu2)*100/@t2,2))+'%' as '次数2比例' from test_table where
id=@id and vdate<=@finish group by convert(nvarchar(4),year(vdate))+'年'+convert(nvarchar(2),month(vdate))+'月',ziduan
union
select '合计','',sum(c1),'100%',sum(c2),'100%' from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id and vdate<=@finish group by ziduan) as temp order by 1,3 desc,5 desc
end

if @start<>'' and @finish=''
begin
select @t1=sum(c1),@t2=sum(c2) from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id and vdate>=@start group by ziduan) as temp
select convert(nvarchar(4),year(vdate))+'年'+convert(nvarchar(2),month(vdate))+'月' as '月份',convert(nvarchar(10),ziduan) as '字段',sum(cishu1) as '次数1',convert(nvarchar(5),round(sum(cishu1)*100/@t1,2))+'%' as '次数1比例',sum(cishu2) as '次数2',convert(nvarchar(5),round(sum(cishu2)*100/@t2,2))+'%' as '次数2比例' from test_table where
id=@id and vdate>=@start group by convert(nvarchar(4),year(vdate))+'年'+convert(nvarchar(2),month(vdate))+'月',ziduan
union
select '合计','',sum(c1),'100%',sum(c2),'100%' from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id and vdate>=@start group by ziduan) as temp order by 1,3 desc,5 desc
end

if @start='' and @finish=''
begin
select @t1=sum(c1),@t2=sum(c2) from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id group by ziduan) as temp
select convert(nvarchar(4),year(vdate))+'年'+convert(nvarchar(2),month(vdate))+'月' as '月份',convert(nvarchar(10),ziduan) as '字段',sum(cishu1) as '次数1',convert(nvarchar(5),round(sum(cishu1)*100/@t1,2))+'%' as '次数1比例',sum(cishu2) as '次数2',convert(nvarchar(5),round(sum(cishu2)*100/@t2,2))+'%' as '次数2比例' from test_table where
id=@id group by convert(nvarchar(4),year(vdate))+'年'+convert(nvarchar(2),month(vdate))+'月',ziduan
union
select '合计','',sum(c1),'100%',sum(c2),'100%' from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id group by ziduan) as temp order by 1,3 desc,5 desc
end
end

[好记忆不如烂笔头abc]

--续上面
--px=''
if @px=''
begin
if @start<>'' and @finish<>''
begin
select @t1=sum(c1),@t2=sum(c2) from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id and vdate>=@start and vdate<=@finish group by ziduan) as temp
select ziduan as '字段',sum(cishu1) as '次数1',convert(nvarchar(5),round(sum(cishu1)*100/@t1,2))+'%' as '次数1比例',sum(cishu2) as '次数2',convert(nvarchar(5),round(sum(cishu2)*100/@t2,2))+'%' as '次数2比例' from test_table where
id=@id and vdate>=@start and vdate<=@finish group by ziduan
union
select '合计',sum(c1),'100%',sum(c2),'100%' from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id and vdate>=@start and vdate<=@finish group by ziduan) as temp order by 2 desc,4 desc
end

if @start='' and @finish<>''
begin
select @t1=sum(c1),@t2=sum(c2) from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id and vdate<=@finish group by ziduan) as temp
select convert(nvarchar(10),ziduan) as '字段',sum(cishu1) as '次数1',convert(nvarchar(5),round(sum(cishu1)*100/@t1,2))+'%' as '次数1比例',sum(cishu2) as '次数2',convert(nvarchar(5),round(sum(cishu2)*100/@t2,2))+'%' as '次数2比例' from test_table where
id=@id and vdate<=@finish group by ziduan
union
select '合计',sum(c1),'100%',sum(c2),'100%' from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id and vdate<=@finish group by ziduan) as temp order by 2 desc,4 desc
end

if @start<>'' and @finish=''
begin
select @t1=sum(c1),@t2=sum(c2) from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id and vdate>=@start group by ziduan) as temp
select convert(nvarchar(10),ziduan) as '字段',sum(cishu1) as '次数1',convert(nvarchar(5),round(sum(cishu1)*100/@t1,2))+'%' as '次数1比例',sum(cishu2) as '次数2',convert(nvarchar(5),round(sum(cishu2)*100/@t2,2))+'%' as '次数2比例' from test_table where
id=@id and vdate>=@start group by ziduan
union
select '合计',sum(c1),'100%',sum(c2),'100%' from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id and vdate>=@start group by ziduan) as temp order by 2 desc,4 desc
end

if @start='' and @finish=''
begin
select @t1=sum(c1),@t2=sum(c2) from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id group by ziduan) as temp
select convert(nvarchar(10),ziduan) as '字段',sum(cishu1) as '次数1',convert(nvarchar(5),round(sum(cishu1)*100/@t1,2))+'%' as '次数1比例',sum(cishu2) as '次数2',convert(nvarchar(5),round(sum(cishu2)*100/@t2,2))+'%' as '次数2比例' from test_table where
id=@id group by ziduan
union
select '合计',sum(c1),'100%',sum(c2),'100%' from (select ziduan,sum(cishu1) as c1,sum(cishu2) as c2 from test_table where id=@id group by ziduan) as temp order by 2 desc,4 desc
end
end


GO

[hello200479]

帮忙顶一下

[muziruyue]

帮你 UP ,要给分