[프로시저] 사용 예(while, if문 사용)

create proc pr_aa
as
begin
  declare @v_name varchar(200)
  declare @v_id int
  declare @v_id_count int
  declare @p_id int
  declare @p_name varchar(200)
  declare @vo_count int
  declare @ch_count int
  declare csr cursor for
  select variablename, variableid, partid from winspc.varble order by variableid
  open csr
  fetch next from csr into @v_name, @v_id, @p_id
  while (@@fetch_status=0)
  begin
    select @vo_count = count(*) from winspc.vviolate where variableid = @v_id
    select @ch_count = count(*) from winspc.dent where charid = @v_id and (severity = 2 or severity = 3 or severity =4)
    select @v_id_count = count(*) from Variable_Monitor where VariableID = @v_id
    select @p_name = partname from winspc.part where partid = @p_id
    if @v_id_count > 0
      update Variable_Monitor set VariableName = @v_name, PartId = @p_id, VoCount = @vo_count, ChCount = @ch_count, PartName = @p_name where VariableID = @v_id
    else
      insert into Variable_Monitor(VariableID, VariableName, PartID, PartName, VoCount, ChCount) values(@v_id, @v_name, @p_id, @p_name, @vo_count, @ch_count)
    fetch next from csr into @v_name, @v_id, @p_id
  end
  close csr
  deallocate csr
end

GO