--创建存储过程
if exists(select 1 from sysobjects where id=object_id('p') and xtype='P')
drop procedure p
go
create procedure p
@startDate datetime, --开始时间
@endDate datetime, --结束时间
@strlist varchar(200), --字符串列表,逗号分割
@path varchar(50) --文件保存路径
as
declare @sql varchar(5000)
declare @filepath varchar(50)
while charindex(',',@strlist)>0
begin
if right(@path,1)<>'\'
set @path=@path+'\'
set @filepath=@path+left(@strlist,charindex(',',@strlist)-1)+'.xls'
set @sql='select * from sqldb.dbo.[table] where dt between convert(datetime,'''+
convert(varchar,@startDate)+''') and convert(datetime,'''+convert(varchar,@endDate)+''') and '+
'charindex('',''+[str]+'','','','+left(@strlist,charindex(',',@strlist)-1)+','')>0'
set @sql='bcp "'+@sql+'" queryout "'+@filepath+'" -c -T'
--print @sql
exec master..xp_cmdshell @sql
set @strlist=stuff(@strlist,1,charindex(',',@strlist),'')
end
set @filepath=@path+@strlist+'.xls'
set @sql='select * from test.dbo.A where dt between convert(datetime,'''+
convert(varchar,@startDate)+''') and convert(datetime,'''+convert(varchar,@endDate)+''') and '+
'charindex('',''+[str]+'','','','+@strlist+','')>0'
set @sql='bcp "'+@sql+'" queryout "'+@filepath+'" -c -T'
exec master..xp_cmdshell @sql
go
--调用
exec p ‘2005-01-01','2005-05-01','aaa,bbb,ccc,ddd,eee','c:'
if exists(select 1 from sysobjects where id=object_id('p') and xtype='P')
drop procedure p
go
create procedure p
@startDate datetime, --2005-01-01
@endDate datetime, --2005-03-01
@strlist varchar(200), --aaa,bbb,ccc,ddd,eee
@path varchar(50), --d:\aaa.xls,d:\bbb.xls,d:\ccc.xls,d:\ddd.xls,d:\eee.xls
as
declare @sql varchar(5000)
declare @filepath varchar(50)
while charindex(',',@strlist)>0
begin
if right(@path,1)<>'\'
set @path=@path+'\'
set @filepath=@path+left(@strlist,charindex(',',@strlist)-1)+'.xls'
set @sql='select * from a.dbo.[table] where dt between convert(datetime,'''+
convert(varchar,@startDate)+''') and convert(datetime,'''+convert(varchar,@endDate)+''') and '+
'charindex('',''+[str]+'','','','+left(@strlist,charindex(',',@strlist)-1)+','')>0'
set @sql='bcp "'+@sql+'" queryout "'+@filepath+'" -c -T'
--print @sql
exec master..xp_cmdshell @sql
set @strlist=stuff(@strlist,1,charindex(',',@strlist),'')
end
set @filepath=@path+@strlist+'.xls'
set @sql='select * from test.dbo.A where dt between convert(datetime,'''+
convert(varchar,@startDate)+''') and convert(datetime,'''+convert(varchar,@endDate)+''') and '+
'charindex('',''+[str]+'','','','+@strlist+','')>0'
set @sql='bcp "'+@sql+'" queryout "'+@filepath+'" -c -T'
exec master..xp_cmdshell @sql
go
--调用
exec p ‘2005-01-01','2005-05-01','aaa,bbb,ccc,ddd,eee','c:'
--创建测试环境
create table A(dt datetime,str varchar(20))
insert A
select getdate(),'AAA' union
select getdate(),'BBB' union
select getdate(),'CCC' union
select getdate(),'DDD' union
select getdate(),'EEE' union
select getdate(),'GGG'
go
--创建存储过程
if exists(select 1 from sysobjects where id=object_id('p') and xtype='P')
drop procedure p
go
create procedure p
@startDate datetime, --开始时间
@endDate datetime, --结束时间
@strlist varchar(200), --字符串列表,逗号分割
@filepath varchar(100) --输出文件路径
as
declare @sql varchar(5000)
set @sql='select * from test.dbo.A where dt between convert(datetime,'''+
convert(varchar,@startDate)+''') and convert(datetime,'''+convert(varchar,@endDate)+''') and '+
'charindex('',''+[str]+'','','','+@strlist+','')>0'
set @sql='bcp "'+@sql+'" queryout "'+@filepath+'" -c -T'
--print @sql
exec master..xp_cmdshell @sql
go
--测试
exec p '1900-1-1','2005-8-3','AAA,CCC','c:\data.xls'