declare @order_ctrl_code varchar(25)
declare @accession_no int
declare @ec tinyint

set @order_ctrl_code = 'KDICTE'
set @accession_no = 123
set @ec = 0

declare @order varchar(25)
declare @count tinyint
declare @index int

/*check how many records are present in the error table for this procedure*/
set @count = (select count(*) from riserrors where accession_no = @accession_no)

/*Check if errors present in the error table*/
if @count <> 0
begin
  /*Delete records for this procedure from the error table if some are present*/
  if @EC = 0
  begin
    /*Check @order_ctrl_code, if value = IEXFIN automatically delete records from the error table as this would
    overwrite any record found there. If not IEXFIN see else statement*/
      if @order_ctrl_code = 'IEXFIN'
      begin
	delete from riserrors where accession_no = @accession_no
      end
      else
      begin
	/*Check order_ctrl_code of errors in the error table. If errors have an IEXFIN order_ctrl_code, do not
	delete that error as it will update the record imported*/
	while @count > 0
	begin
	  set @index = (select top 1 rec_index from riserrors where accession_no = @accession_no 
	  		and rec_index > @index order by rec_index)
	  set @order = (select order_ctrl_code from riserrors where rec_index = @index)
	  if @order <> 'IEXFIN'
	  begin
	    if @order <> 'KDICTE'
	    begin
	      delete from riserrors where rec_index = @index
	      set @count = @count - 1
		if @count = 0
		  break
		else
		  continue
	    end
	    else
	    begin
	      if @order_ctrl_code = 'KDICTE'
	      begin
		delete from riserrors where rec_index = @index
	        set @count = @count - 1
		  if @count = 0
		    break
		  else
		    continue
	      end
	      else
	      begin
	        set @count = @count - 1
		  if @count = 0
		    break
		  else
		    continue
	      end
	    end
	  end
	  else
	  begin
	    set @count = @count - 1
	      if @count = 0
		break
	      else
		continue
	  end  
        end
      end
   end
end

quote:

---

Originally posted by visakh16

i think issue is this where condition

rec_index > @index

you've not assigned @index a value so it will be NULL by default and hence this condition wont return any value under default conditions as NULL is not stored as a value. try this instead

rec_index > coalesce(@index ,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---