SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
declare @tmpseqno char(6)
declare @tmpitemno char(10)
declare @tmpmaxint int
declare @tmpid int--游标读取明细
DECLARE curdtld CURSOR FOR select category,id from t_testbaihuo order by id
OPEN curdtld
FETCH NEXT FROM curdtld INTO @tmpseqno,@tmpid
select @@FETCH_STATUS
return
WHILE @@FETCH_STATUS=0 BEGIN
-----生产itemno
if (select count(*) from t_testbaihuo where category =@tmpseqno)<1
begin
select @tmpitemno=@tmpseqno+'0001'
end
else
begin
select @tmpmaxint=max(substring(seqno,7,4))from t_testbaihuo where category=@tmpseqno
select @tmpitemno = @tmpseqno+dbo.PadLeft(@tmpmaxint+1,'0',4)
end update t_testbaihuo set seqno=@tmpitemno where id=@tmpid FETCH NEXT FROM curdtld INTO @tmpseqno,@tmpid
END
CLOSE curdtld
DEALLOCATE curdtld
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO@@FETCH_STATUS
打印出来为-1 请求解决 表内有数据的
GO
SET ANSI_NULLS ON
GO
declare @tmpseqno char(6)
declare @tmpitemno char(10)
declare @tmpmaxint int
declare @tmpid int--游标读取明细
DECLARE curdtld CURSOR FOR select category,id from t_testbaihuo order by id
OPEN curdtld
FETCH NEXT FROM curdtld INTO @tmpseqno,@tmpid
select @@FETCH_STATUS
return
WHILE @@FETCH_STATUS=0 BEGIN
-----生产itemno
if (select count(*) from t_testbaihuo where category =@tmpseqno)<1
begin
select @tmpitemno=@tmpseqno+'0001'
end
else
begin
select @tmpmaxint=max(substring(seqno,7,4))from t_testbaihuo where category=@tmpseqno
select @tmpitemno = @tmpseqno+dbo.PadLeft(@tmpmaxint+1,'0',4)
end update t_testbaihuo set seqno=@tmpitemno where id=@tmpid FETCH NEXT FROM curdtld INTO @tmpseqno,@tmpid
END
CLOSE curdtld
DEALLOCATE curdtld
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO@@FETCH_STATUS
打印出来为-1 请求解决 表内有数据的
declare @tmpitemno char(10)
declare @tmpmaxint int
declare @tmpid int--游标读取明细
DECLARE curdtld CURSOR FOR select category,id from t_testbaihuo order by id
OPEN curdtld
FETCH NEXT FROM curdtld INTO @tmpseqno,@tmpid
WHILE @@FETCH_STATUS=0 BEGIN
-----生产itemno
if (select count(*) from t_testbaihuo where category =@tmpseqno)<1
begin
select @tmpitemno=@tmpseqno+'0001'
end
else
begin
select @tmpmaxint=max(substring(seqno,7,4))from t_testbaihuo where category=@tmpseqno
select @tmpitemno = @tmpseqno+dbo.PadLeft(@tmpmaxint+1,'0',4)
end update t_testbaihuo set seqno=@tmpitemno where id=@tmpid FETCH NEXT FROM curdtld INTO @tmpseqno,@tmpid
END
CLOSE curdtld
DEALLOCATE curdtld
这个数据量如何呢