create procedure statisticsDis
as
declare @schoolId nvarchar(2),@schoolName nvarchar(50),@schools int
declare @signNum int
select @schools=count(*) from tSchool
delete from tStatSchool
declare schoolCur cursor forward_only for select schoolId,schoolName from tSchool order by schoolId
open schoolCur
while @schools!=0
begin
fetch next from schoolCur into @schoolId,@schoolName
select @signNum=count(*) from tCompetitor where schoolId = @schoolId
insert into tStatSchool(schoolId,schoolName,signNum) select @schoolId,@schoolName,@signNum
@schools=@schools-1
end
close schoolCur
deallocate schoolCur
go