存储过程问题！在线等！重谢！
建立一个存储过程
数据库名为：sqldb 表名为：table 日期条件为：table中的一个时间列大于2005－01－01小于2005－05－01
字符条件为：table表中的一个字符串列分别等于aaa或bbb或ccc或ddd或eee，每个字符条件也都必须符合上述时间范围
将每一个字符条件存为一个文件，例如：c:\aaa.xls,c:\bbb.xls,c:\ccc.xls,c:\ddd.xls,c:\eee.xls
也就是说，在2005－01－01小于2005－05－01这个时间范围内字符串列分别等于aaa或bbb或ccc或ddd或eee时的所以记录。
万分感谢！！！
问题点数：100、回复次数：10Top
1 楼zjcxc（邹建）回复于 2005-08-03 16:56:08 得分 50
use sqldb
go
-- 存储过程
create proc p_export
@str nvarchar(4000)
as
declare @s nvarchar(4000)
WHILE CHARINDEX(',',@str)>0
BEGIN
select @s='bcp "select * from sqldb..[table] where 日期列>''2005-01-01'' and 日期列<''2005-01-01''
and 字符串列='''+replace(LEFT(@str,CHARINDEX(',',@str)-1),'''','''''')+'''" queryout "c:\'
+LEFT(@str,CHARINDEX(',',@str)-1)+'.xls" /T /c',
@str=STUFF(@str,1,CHARINDEX(',',@str),'')
exec master..xp_cmdshell @s,no_output
END
select @s='bcp "select * from sqldb..[table] where 日期列>''2005-01-01'' and 日期列<''2005-01-01''
and 字符串列='''+replace(@str,'''','''''')+'''" queryout "c:\'
+@str+'.xls" /T /c'
exec master..xp_cmdshell @s,no_output
Top
2 楼zjcxc（邹建）回复于 2005-08-03 16:58:57 得分 0
--不要换行
use sqldb
go
-- 存储过程
alter proc p_export
@str nvarchar(4000)
as
declare @s nvarchar(4000)
WHILE CHARINDEX(',',@str)>0
BEGIN
select @s='bcp "select * from sqldb..[table] where 日期列>''2005-01-01'' and 日期列<''2005-01-01'' and 字符串列='''+replace(LEFT(@str,CHARINDEX(',',@str)-1),'''','''''')+'''" queryout "c:\'
+LEFT(@str,CHARINDEX(',',@str)-1)+'.xls" /T /c',
@str=STUFF(@str,1,CHARINDEX(',',@str),'')
exec master..xp_cmdshell @s,no_output
END
select @s='bcp "select * from sqldb..[table] where 日期列>''2005-01-01'' and 日期列<''2005-01-01'' and 字符串列='''+replace(@str,'''','''''')+'''" queryout "c:\'
+@str+'.xls" /T /c'
exec master..xp_cmdshell @s,no_output
Top
3 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-08-03 17:04:04 得分 50
--生成测试数据
use sqldb
go
create table table(name varchar(20),dates datetime)
insert into [table] select 'aaa','2005-01-01'
insert into [table] select 'bbb','2005-01-01'
insert into [table] select 'ccc','2005-01-01'
insert into [table] select 'ddd','2005-01-01'
insert into [table] select 'eee','2005-01-01'
go
--创建存储过程
create procedure sp_bcp(@strdate datetime,@enddate datetime)
as
begin
declare @name varchar(20)
declare t_cursor cursor for
select distinct name from t where dates between @strdate and @enddate
open t_cursor
fetch next from t_cursor into @name
while @@fetch_status = 0
begin
exec master..xp_cmdshell 'bcp "select * from sqldb.dbo.[table] where id='''+@name+''' and dates between '''+@strdate+''' and '''+@enddate+'''" queryout "C:\'+@name+'.xls" -c -q -U"sa" -P"xxx"',no_output
fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor
end
go
--执行存储过程
exec sp_bcp '2005-01-01','2005-05-01'Top
4 楼surn（孤影.*）回复于 2005-08-03 17:05:41 得分 0
xuexiTop
5 楼phantomMan（）回复于 2005-08-03 17:07:07 得分 0
create table table1(
id int identity,
datefield datetime,
strfield varchar(20)
)
insert into table1 values('2004-01-01','aaa')
insert into table1 values('2005-01-07','aaa')
insert into table1 values('2005-01-07','bbb')
insert into table1 values('2005-01-23','bbb')
select * from table1
--导出数据:
exec master..xp_cmdshell 'bcp "select * from ColinTest..table1 where datefield>''2005-01-01'' and datefield<''2005-05-01'' and strfield=''aaa'' " queryout c:\aaa.xsl -c'
exec master..xp_cmdshell 'bcp "select * from ColinTest..table1 where datefield>''2005-01-01'' and datefield<''2005-05-01'' and strfield=''bbb'' " queryout c:\bbb.xsl -c'
exec master..xp_cmdshell 'bcp "select * from ColinTest..table1 where datefield>''2005-01-01'' and datefield<''2005-05-01'' and strfield=''ccc'' " queryout c:\ccc.xsl -c'
exec master..xp_cmdshell 'bcp "select * from ColinTest..table1 where datefield>''2005-01-01'' and datefield<''2005-05-01'' and strfield=''ddd'' " queryout c:\ddd.xsl -c'
exec master..xp_cmdshell 'bcp "select * from ColinTest..table1 where datefield>''2005-01-01'' and datefield<''2005-05-01'' and strfield=''eee'' " queryout c:\eee.xsl -c'
Top
6 楼lqxty（）回复于 2005-08-04 00:00:39 得分 0
libin_ftsafe(子陌红尘) :好像不行啊！我在哪里填写我的条件呢？aaa，bbb，ccc，ddd，eee
我要的是在这个时间段内，每个符合aaa，bbb，ccc，ddd，eee的条件的数据！
就是，在这个时间段内字符串为aaa的有30条数据，存为aaa.xls
在这个时间段内字符串为bbb的有1000条数据，存为bbb.xls
依此类推！我一定结！Top
7 楼zjcxc（邹建）回复于 2005-08-04 12:40:52 得分 0
我的呢?Top
8 楼chenqianlong（443）回复于 2005-08-04 13:36:20 得分 0
看Top
9 楼phantomMan（）回复于 2005-08-04 13:51:58 得分 0
我上面的不行吗?/
我自己测试过,完全可以的Top
10 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-08-04 14:06:11 得分 0
以下代码通过测试：
------------------------------------------------------------
--生成测试数据
use sqldb
go
create table table(name varchar(20),dates datetime)
insert into [table] select 'aaa','2005-01-01'
insert into [table] select 'bbb','2005-01-01'
insert into [table] select 'ccc','2005-01-01'
insert into [table] select 'ddd','2005-01-01'
insert into [table] select 'eee','2005-01-01'
go
--创建存储过程
create procedure sp_bcp(@strdate datetime,@enddate datetime)
as
begin
declare @name varchar(20),@s varchar(8000)
declare t_cursor cursor for
select distinct name from [table] where dates between @strdate and @enddate
open t_cursor
fetch next from t_cursor into @name
while @@fetch_status = 0
begin
set @s = 'bcp "select * from sqldb..[table] where name='''
+@name+''' and dates between '''
+convert(char(10),@strdate,120)+''' and '''
+convert(char(10),@enddate,120)+'''" queryout "C:\'
+@name+'.xls" -c -q -U"sa" -P"xxx"' --需替换用户名和密码
print @s
exec master..xp_cmdshell @s,no_output
fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor
end
go
--执行存储过程
exec sp_bcp '2005-01-01','2005-05-01'
goTop
