how to resume a loop in a procedure on error

[bebblebrox]

I have a script which uses a cursor (cursor use is not the topic though ). There are some records that, for various reasons, cause an errors such as:

1. can't insert null
2. data would have been truncated
3. eetc

which cause the statement to be aborted. My question is: what construct do i need to use in order for the procedure to continue.

my script looks something like:

Declare vars



--create cursor of existing records
DECLARE cMembers CURSOR for blah


begin
open cMembers
fetch next from cMembers
while @@FETCH_STATUS = 0 Begin


	
	fetch next from cMembers into @vars
		
	--clean up data
	
	--insert record
	insert into table   (sometimes this causes errors)
	(
	columns
	)
	
	values
	
	(
	@vars
	)
	  
	set @ErrorNo = @@Error
	
	if @ErrorNO <> 0
	  Begin
	    --do something
	  End
	

	Begin
	INSERT INTO table  (sometimes this causes errors)
	SELECT columns
	FROM table
	where conditions
        set @ErrorNo = @@Error
		
	if @ErrorNO <> 0
	Begin
	  --do something
	End
	  
	  end

	
	  
	  
end

close cMembers
deallocate cMembers
end

when the above inserts cause errors, i want to take some action and then move on to the next record in the cursor.

what am i doing wrong?

thanks!

 

[NoCoolHandle]

depending on if you want to do something or see that you have done something, the key might be how you execute the script.

I found, and reported it on the MS BB, that you can actually have script execute, but no see the results of that execution if an error has occoured.

Strangley everyone on the MS site seemed to think that this behavour was ok. The general concensis seemed to be that it was ok for it to work in QA and that you shouldn't expect to see the results via ADO or ADO.Net. In both of the client libs it seems ok that the first error (even one that doesnt hit your business logic as being critical) should be raised to the client and all other communitcation from that point on is lost.

About the only way to tell it to check your table afterwards.

SUCKS huh.

 

[mrdenny]

Weither or not the loop will continue will depend on the severity of the error which is raised. Some errors will cause the script to throw an error, and some will cause the script to completly bomb out.

I'd recommend adding in code that checks the data and validates it before entering it into the table. If you find an exception insert the record into another table and work from there.


Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)