帮我把 这段存储过程还成sql2000能用的
CREATE PROCEDURE test
AS
BEGIN
SET NOCOUNT ON;
select *,row_number() over (partition by sn order by arDate) re
into #cu1
from 表1 where states=0
select a.*
into #cu2
from #cu1 a
inner join #cu1 b on a.sn=b.sn and a.re-b.re=1
where datediff(second,b.arDate,a.arDate)/60>=10
declare @err int
begin Tran
insert 表2 (id,SN,Name,arDate)
select id,SN,Name,arDate from #cu2
union all
select id,SN,Name,arDate from #cu1
where re=1 and sn in (select distinct sn from #cu2)
order by SN,arDate
set @err=@@error
if @err>0
goto label_end
update 表2 set states=1 where sn in (select distinct sn from #cu1)
set @err=@@error
label_end:
if @@err>0
rollback Tran
else
commit Tran
end
go 存储 SQL select Go
[解决办法]
是哪边报错导致你这段存储过程在SQL2000上不能使用？
[解决办法]
row_number() over (partition by sn order by arDate)
是sql server 2005及以上的语句.
如果是sql 2000,则可采用如下的方法:
select t.*,(select count(1) from 表1 where sn = t.sn and ardate < t.ardate) + 1 re
into #cu1
from 表1 t where states=0
