表A有几个字段:ID varchar(32);Date datetime,TimeSpan varchar(12),CanUsed char(1)
意思分别是编号,开始停牌时间,停牌多久(数值+单位),状态现在要写一SQL语句,更新数据库中的记录:如果系统当前时间小于Date,则设置状态值为1;
如果系统时间大于Date,而小于dateadd(@timeunit,@timecount,@date);【其中@timeunit为TimeSpan的后2位,即单位(mm,dd,hh,mi,ss等),@timecount为TimeSpan的除最后两位外的整数部分】,此时设置状态值为0;
如果系统时间大于dateadd(@timeunit,@timecount,@date),则设置状态值为2;请按照要求写一SQL语句或者存储过程。
谢谢
意思分别是编号,开始停牌时间,停牌多久(数值+单位),状态现在要写一SQL语句,更新数据库中的记录:如果系统当前时间小于Date,则设置状态值为1;
如果系统时间大于Date,而小于dateadd(@timeunit,@timecount,@date);【其中@timeunit为TimeSpan的后2位,即单位(mm,dd,hh,mi,ss等),@timecount为TimeSpan的除最后两位外的整数部分】,此时设置状态值为0;
如果系统时间大于dateadd(@timeunit,@timecount,@date),则设置状态值为2;请按照要求写一SQL语句或者存储过程。
谢谢
1001 SZ600001 2010-12-25 0:00:00 10mi 1
1002 SZ600002 2010-6-30 0:00:00 10ss 0
1010 SZ600010 2010-6-25 0:00:00 5dd 1
1011 SH000011 2010-7-1 0:00:00 5dd 0
1001 SZ600001 2010-12-25 0:00:00 10hh 1
1002 SZ600002 2010-6-30 0:00:00 10hh null
1010 SZ600010 2010-6-25 0:00:00 5dd 1
1011 SH000011 2010-7-10 0:00:00 8mm 0
这些记录的CanUsed字段是错误,现在要更新这个字段
set @dd=getdate()
Update A Set CanUsed =(Case When @dd<Date Then '1' When @dd>Date And @dd<dateadd(@timeunit,@timecount,@date) Then '0' When @dd>dateadd(@timeunit,@timecount,@date) Then '2' Else null End)
这种使用方法好像有误
再说@timeunit,@timecount还没声明啊?
when Date between getdate() and dateadd(right(TimeSpan,2),convert(int,left(TimeSpan,len(TimeSpan)-2)),Date) then 0
when Date>dateadd(right(TimeSpan,2),convert(int,left(TimeSpan,len(TimeSpan)-2)),Date) then 2 end
update tablename set CanUsed=case when getdate()<Date then 1
when Date between getdate() and dateadd(right(TimeSpan,2),convert(int,left(TimeSpan,len(TimeSpan)-2)),Date) then 0
when Date>dateadd(right(TimeSpan,2),convert(int,left(TimeSpan,len(TimeSpan)-2)),Date) then 2 else null end
update tablename set CanUsed=case when getdate()<Date then 1
when getdate() between Date and dateadd(right(TimeSpan,2),convert(int,left(TimeSpan,len(TimeSpan)-2)),Date) then 0
when getdate()>dateadd(right(TimeSpan,2),convert(int,left(TimeSpan,len(TimeSpan)-2)),Date) then 2 else null end
不能是变量
这地方得处理一下
AS
declare @time varchar(20),@date datetime,@timeunit varchar(50),@timecount int,@id varchar(32),@stoptime datetime,@flag char(1)
declare cursor_suspension cursor
for
select ID,Date,TimeSpan from ChinaStockSuspension where CanUsed='0' or CanUsed='1'
open cursor_suspension
fetch next from cursor_suspension into @id,@date,@time
while @@FETCH_STATUS=0
begin--select @id=ID,@date=Date,@time=TimeSpan from ChinaStockSuspension where CanUsed='0' or CanUsed='1'
if(@time='' or @time is null)
begin
delete from ChinaStockSuspension where ID=@id
end
if(len(@time)<=2)
begin
delete from ChinaStockSuspension where ID=@id
end
else if(len(@time)>=3)
begin
--字符串类型的StopTime字段的单位
set @timeunit=substring(@time,len(@time)-1,2)
--字符串类型的StopTime字段的数值
set @timecount=substring(@time,0,len(@time)-1)
if(@timeunit='mm')
begin
set @stoptime=dateadd(mm,@timecount,@date)
end
else if(@timeunit='dd')
begin
set @stoptime=dateadd(dd,@timecount,@date)
end
else if(@timeunit='hh')
begin
set @stoptime=dateadd(hh,@timecount,@date)
end
else if(@timeunit='mi')
begin
set @stoptime=dateadd(mi,@timecount,@date)
end
else if(@timeunit='ss')
begin
set @stoptime=dateadd(ss,@timecount,@date)
end
else
begin
set @stoptime=getdate()
end
if(getdate()<@date)
begin
update ChinaStockSuspension
set CanUsed='1' where CanUsed<>'2' and getdate()<@date
end
else if(getdate()>=@date and getdate()<=@stoptime)
begin
update ChinaStockSuspension
set CanUsed='0' where CanUsed<>'2' and getdate()>=@date and getdate()<=@stoptime
end
else if(getdate()>@stoptime)
begin
update ChinaStockSuspension
set CanUsed='2' where CanUsed<>'2' and getdate()>@stoptime
end
else
begin
update ChinaStockSuspension
set CanUsed='' where CanUsed<>'2' and getdate()>@stoptime
endendend
--关闭游标,释放游标变量
Close cursor_suspension
Deallocate cursor_suspension请问一下这个存储过程哪里有问题,执行好长时间都没有结果
for select ID,TimeSpan from abc
open aaa
declare @ID varchar(20),@TimeSpan varchar(20)
fetch next from aaa into @ID,@TimeSpan
while @@fetch_status=0
begin
declare @str varchar(2000),@mm varchar(20),@num varchar(20)
select @mm=right(@TimeSpan,2),@num=left(@TimeSpan,len(@TimeSpan)-2)
set @str='update abc set CanUsed=case when getdate()<Date then 1
when getdate() between Date and dateadd('+@mm+','+@num+',Date) then 0
when getdate()>dateadd('+@mm+','+@num+',Date) then 2 else null end where ID='+@ID
exec (@str)
fetch next from aaa into @ID,@TimeSpan
end
close aaa
deallocate aaa
insert aa_ select 1001 ,'SZ600001', '2010-12-25 0:00:00', '10mi', 1
union all select 1002 ,'SZ600002', '2010-6-30 0:00:00', '10ss', 0
union all select 1010 ,'SZ600010', '2010-6-25 0:00:00', '5dd', 1
union all select 1011 ,'SH000011', '2010-7-1 0:00:00', '5dd', 0
union all select 1001 ,'SZ600001', '2010-12-25 0:00:00', '10hh', 1
union all select 1002 ,'SZ600002', '2010-6-30 0:00:00', '10hh', null
union all select 1010 ,'SZ600010', '2010-6-25 0:00:00', '5dd', 1
union all select 1011 ,'SH000011', '2010-7-10 0:00:00', '8mm', 0
execmaster..xp_execresultset 'SELECT
''update aa_ set CanUsed=
CASE WHEN DATEDIFF(ss,date,GETDATE())>0 THEN 1
WHEN DATEDIFF(ss,date,GETDATE())<0 AND DATEDIFF(ss,GETDATE(),DATEADD(''+RIGHT(TimeSpan,2)+'',''+LEFT(TimeSpan,LEN(TimeSpan)-2)+'',date))>0
THEN 0
WHEN DATEDIFF(ss,GETDATE(),DATEADD(''+RIGHT(TimeSpan,2)+'',''+LEFT(TimeSpan,LEN(TimeSpan)-2)+'',date))<0 THEN 2
END ''
FROM aa_',N'库名'
go
SELECT * FROM aa_
Create proc Test_Sp_A As
set nocount on
Begin--1
Declare @now datetime,@timeunit varchar(2),@ti int,@dd datetime
Declare @ID varchar(32),@Date datetime,@TimeSpan varchar(12),@CanUsed char(1),@num int
Declare curA cursor For Select [num],[ID],[Date],[TimeSpan],[CanUsed] From A
Set @now =getdate()
Open curA
Fetch curA Into @num,@ID,@Date,@TimeSpan,@CanUsed
While (@@fetch_status = 0)
Begin--2
If @now<@Date
Update A Set CanUsed='1' Where num=@num
Else
Begin--3
Set @timeunit=Right(@TimeSpan,2)
Set @ti=convert(int,left(@TimeSpan,len(@TimeSpan)-2))
Set @dd=Case @timeunit When 'mm' Then dateadd(mm,@ti,@Date)
When 'dd' Then dateadd(dd,@ti,@Date)
When 'wk' Then dateadd(wk,@ti,@Date)
When 'hh' Then dateadd(hh,@ti,@Date)
When 'mi' Then dateadd(mi,@ti,@Date)
When 'ss' Then dateadd(ss,@ti,@Date)
Else @now End
If @now>@dd
Update A Set CanUsed='2' Where num=@num
Else
Update A Set CanUsed='0' Where num=@num
End--3
Fetch curA Into @num,@ID,@Date,@TimeSpan,@CanUsed
End--2
Close curA
Deallocate curA
End--1
Exec Test_Sp_A
Select * From A
1 1001 2010-12-25 00:00:00.000 10mi 1
2 1002 2010-06-30 00:00:00.000 10ss 2
NULL 1010 2010-06-25 00:00:00.000 5dd 0
4 1011 2010-07-01 00:00:00.000 5dd 0
5 1001 2010-12-25 00:00:00.000 10hh 1
6 1002 2010-06-30 00:00:00.000 10hh 2
NULL 1010 2010-06-25 00:00:00.000 5dd 0
8 1011 2010-07-01 00:00:00.000 5mm 0其中第三条记录和第七条记录因为没有加上主键,所以结果不正确。