create proc sp_update
@Classid int,
@ID int
as
set nocount on
update tb_ArticleClass
set ArticleCount=(select count(*) as count from tb_Article
where ClassID =@ClassID
and ID=@ID
go--调用:
exec sp_update classid的值,ID的值
@Classid int,
@ID int
as
set nocount on
update tb_ArticleClass
set ArticleCount=(select count(*) as count from tb_Article
where ClassID =@ClassID
and ID=@ID
go--调用:
exec sp_update classid的值,ID的值
as
beginDECLARE cur CURSOR
FOR select ID from tb_ArticleClass where ParentID <>0DECLARE @id intOPEN cur
FETCH NEXT FROM cur INTO @idwhile @@fetch_status=0
BEGIN
update tb_ArticleClass set ArticleCount=
(select count(*) as count from tb_Article where ClassID=@id)
where ID=@id
FETCH NEXT FROM cur INTO @id
ENDCLOSE cur
DEALLOCATE cur
end
GO
exec proc_update
update tb_ArticleClass
set ArticleCount=(select count(1) as count from tb_Article b where b.ClassID =a.id)
from tb_ArticleClass a
where ID in (select ID from tb_ArticleClass where ParentID <>0)
不需要传递参数,也不需要游标
declare @id int
--定义游标
declare DZCursor CURSOR for select ID from tb_ArticleClass where ParentID <>0
--打开游标
open DZCursor
--取记录
fetch next from DZCursor into @id
--存在记录
while @@fetch_status=0
begin
update tb_ArticleClass set ArticleCount=(select count(*) as count from tb_Article where ClassID =@id) where ID=@id
fetch next from DZCursor into @id
end
--关闭游标
close DZCursor
--删除游标
deallocate DZCursor