没写过复杂的存储过程，谁给看看这个怎么写
写个存储过程
SP_HKeep_Log
返回值
0：执行成功
1：失败
处理过程
1.得到当前的业务日期
select date from A
2.把得到的时间转化为字符串
格式为：YYYYMMDD
3.得到日志文件保存天数
select date_value from B where date_id=008 and data_typ_fg=0
4.在目录中：E:\progect\LOG
4.1 遍历所有文件
4.2 得到文件名，把文件后八位转化为日期DtlogFile
4.3 如果DtlogFile-date大于LogKeepD则 删除文件
5.退出 0
多谢
[解决办法]
以前用过的跟你的有点象
alter proc usp_delete_log (@path varchar(120),@dbname varchar(32), @daynum smallint) -- usp_delete_log 'E:\progect\LOG','pubs',5 总是保持最近5天以内的日志-- 日志格式为 pubs_log_20060505.log-- 下为备份过程/*create procedure usp_log_backup(@path varchar(120),@dbname varchar(32)) as declare @curtime char(10) declare @cmdstr varchar(120) set @curtime=select convert(char(13),getdate(),112) ----20070319 set @cmdstr=' backup log '+@dbname+' to disk ='''+@path+'\'+@dbname+'_log_'+@curtime+'.log'''+' with init' execute(@cmdstr)*/as begin tran declare @curtime varchar(20) declare @cmdstr1 varchar(120) declare @cmdstr2 varchar(120) declare @filename varchar(80) CREATE TABLE #fname1 ( fname varchar(80)) CREATE TABLE #fname2 ( fname varchar(80)) set @curtime=convert(char(8),cast(datediff(dd,@daynum,getdate()) as smalldatetime),112)---20061001 set @cmdstr1=' exec master.dbo.xp_cmdshell ''dir '+@path+'\'+@dbname+'*.log/b''' INSERT #fname1 execute(@cmdstr1) insert #fname2 select * from #fname1 where cast(left(right(fname,12),8) as int)<cast(@curtime as int) declare tb cursor for select fname from #fname2 where fname is not null declare @fname varchar(80) open tb fetch next from tb into @fname while @@fetch_status=0 begin set @cmdstr2='exec master.dbo.xp_cmdshell ''del '+@path+'\'+@fname+'''' exec(@cmdstr2) fetch next from tb into @fname end close tb deallocate tb drop table #fname2 rollback tran
