sql导出查询数据

if exists(select 1 from sysobjects where name ='proc_exporttb' and xtype ='P')
    drop proc proc_exporttb
go    
create proc proc_exporttb
@sqlstr varchar(max),                        --查询语句,如果查询语句中使用了order by ,请加上top 100 percent
@path nvarchar(1000),                        --文件存放目录
@fname nvarchar(250),                        --文件名
@sheetname varchar(250)='\'                --要创建的工作表名,默认为文件名
as 
begin
 declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
 declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)

--参数检测
if isnull(@fname,'\')='\'        set @fname='temp.xls\'
 if isnull(@sheetname,'\')='\' set @sheetname=replace(@fname,'.\','#\')

--检查文件是否已经存在
if right(@path,1)<>'\\\' set @path=@path+'\\\'
 create table #tb(a bit,b bit,c bit)
 set @sql=@path+@fname
 insert into #tb exec master..xp_fileexist @sql

--数据库创建语句
set @sql=@path+@fname
 if exists(select 1 from #tb where a=1)
         set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN=\''\'';READONLY=FALSE\'
                             +';CREATE_DB=\"\'+@sql+'\";DBQ=\'+@sql
 else
         set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 5.0;HDR=YES\'
                                 +';DATABASE=\'+@sql+'\"\'

--连接数据库
exec @err=sp_oacreate 'adodb.connection\',@obj out
 if @err<>0 goto lberr

 exec @err=sp_oamethod @obj,'open\',null,@constr
 if @err<>0 goto lberr

--创建表的SQL
 declare @tbname sysname
 set @tbname='##tmp_\'+convert(varchar(3),newid())
 set @sql='select * into [\'+@tbname+'] from(\'+@sqlstr+') a\'
 exec(@sql)

 select @sql='\',@fdlist='\'
 select @fdlist=@fdlist+',[\'+a.name+']\'
         ,@sql=@sql+',[\'+a.name+'] \'
                 +case when b.name in('char\','nchar\','varchar\','nvarchar\') then
                                  'text(\'+cast(case when a.length>255 then 255 else a.length end as varchar)+')\'
                         when b.name in('tynyint\','int\','bigint\','tinyint\') then 'int\'
                         when b.name in('smalldatetime\','datetime\') then 'datetime\'
                         when b.name in('money\','smallmoney\') then 'money\'
                         else b.name end
 FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
 where b.name not in('image\','text\','uniqueidentifier\','sql_variant\','ntext\','varbinary\','binary\','timestamp\')
         and a.id=(select id from tempdb..sysobjects where name=@tbname)
 select @sql='create table [\'+@sheetname
         +'](\'+substring(@sql,2,8000)+')\'
         ,@fdlist=substring(@fdlist,2,8000)

 exec @err=sp_oamethod @obj,'execute\',@out out,@sql
 if @err<>0 goto lberr

 exec @err=sp_oadestroy @obj
 --导入数据
set @sql='openrowset(\''MICROSOFT.JET.OLEDB.4.0\'',\''Excel 5.0;HDR=YES
                         ;DATABASE=\'+@path+@fname+'\'',[\'+@sheetname+'$])\'

 exec('insert into \'+@sql+'(\'+@fdlist+') select \'+@fdlist+' from [\'+@tbname+']\')

 set @sql='drop table [\'+@tbname+']\'
 exec(@sql)
 return

 lberr:
         exec sp_oageterrorinfo 0,@src out,@desc out
 lbexit:
         select cast(@err as varbinary(4)) as 错误号
                ,@src as 错误源,@desc as 错误描述
        select @sql,@constr,@fdlist
end
go

--exec  proc_exporttb @sqlstr='select * from dbo.ClientLevel\',@path='d:\\\',@fname='aa.xls\',@sheetname='ClientLevel\'