@@fetch_status always equals -1 1

[KDavie]

I have a cursor that I am creating to update data in a table. Even though I am certain there are records being returned in the cursor select statement (I know this because when I run the select along, it returns 3600 rows), the @@fetch_status is always -1. Can anyone tell me why this would be?

Basically, the query looks like this:

BEGIN TRAN
	DECLARE @@Id int
	DECLARE @@Name int
	DECLARE My_Cursor CURSOR
	FOR (
		select a.column1, p.column1
		from table1 a join table2 p ON a.column1 = p.column2
		where p.column1 NOT LIKE '%[^0-9]%' and p.column1 != '' and LEN(p.column1) < 20
		group by  a.column1, p.column1
	)
	OPEN My_Cursor
	 PRINT @@fetch_status
	 WHILE @@fetch_status<> -1
	  BEGIN
	  FETCH next FROM My_Cursor INTO @@Id, @@Name
	  UPDATE table1 SET column1 = @@Name WHERE column1 = @@Id
	  DELETE FROM table2 WHERE column1 = CAST(@@Name as varchar(20))
	 END
	CLOSE My_Cursor
	DEALLOCATE My_Cursor
ROLLBACK TRAN

When I ran it in test it worked fine... in production, however, no luck. Again, when I run just the select statement I am getting over 3600 rows back... so there should be data in the cursor...right?

Thanks,

-Kevin

Kevin Davie
Consultant
Sogeti USA

http://www.us.sogeti.com

 

[SQLDenis]

you also need
FETCH FROM My_Cursor INTO @@Id, @@Name
before the WHILE

However you do not need a cursor for this, a set based solution which will run many times faster can be easily done

a simple update join with the query should do it
same for the delete



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[djj55]

Also, to check, would you not want the print inside the loop?
djj

 

[SQLSister]

Denis is correct never, ever use a cursor to run insert, update or delete statements. They are very slow compared to set-based statements. These are easliy converted to set based statments. Look in BOL on how to do updates and deletes with joins and inserts using selct staments instead of values statements and you will have what you need to avoid ever using a cursor again to modify data.



"NOTHING is more important in a database than integrity." ESquared

 

[KDavie]

great... thanks for all the replies... I will look at the alternative.

Kevin Davie
Consultant
Sogeti USA

http://www.us.sogeti.com

 

[TysonLPrice]

Look in BOL on how to do updates and deletes with joins and inserts using selct staments instead of values statements and you will have what you need to avoid ever using a cursor again to modify data.

I've been a member here for a number of years and read many times not to use a cursor. I'm an applications programmer that needs to write SQL scripts only occasionally. I wrote this to close claims. Could someone see how not to use a cursor/loop for this process? I'm not looking for a rewrite. Just some pseudo code. The input is a list of claims provided to me from a spreadsheet that I load to a table. I left out all the declarations.

DECLARE MyCursor CURSOR FOR SELECT pkClaim FROM MedClaims_06_29_2007

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @pkClaim 

WHILE (@@FETCH_STATUS <> -1)
BEGIN

	-- Initialize the work fields

	set @ClaimReserve_Medical = 0.00
	set @ClaimReserve_Expense = 0.00
	set @ClaimReserve_Comp = 0.00
	set @ClaimReserve_Legal = 0.00
	set @ReturnMedicalPTD = 0.00
	set @ReturnExpensePTD = 0.00
	set @ReturnLegalPTD = 0.00
	set @ReturnCompPTD= 0.00
	set @WriteNoteAndHistory = 0
	set @MYfkParent = 0
	
	-- Get the PTD dollars for this claim.
	exec cst_ClaimPTD
	@fkClaim = @pkClaim,
	@SupressSelect = 1, 
	@MedicalPTD = @ReturnMedicalPTD output,
	@ExpensePTD = @ReturnExpensePTD output,
	@LegalPTD = @ReturnLegalPTD output,
	@CompPTD = @ReturnCompPTD output
	
	
	-- PROCESS MEDICAL
	set @ClaimReserve_Medical =
	(select ReserveAmt from ClaimReserve Where fkClaim = @pkClaim and ReserveType = 'Med' and History = 0)
	if @ClaimReserve_Medical <> 0
		Begin
			update claimreserve set History = 1 where fkClaim = @pkClaim and ReserveType = 'Med' 
			insert into ClaimReserve (fkClaim, ReserveType, ReserveAmt, History, DateLup, UserLup, ChangeReason)
				        Values(@pkClaim,'Med',@ReturnMedicalPTD,0,@GetDate,'PriceT','Closing claim per policy.  System generated per xxxx.')	
			set @WriteNoteAndHistory = 1
			update claim set tpaclaimstatus = 'Closed', fkTPAClaimStatusReason = 15, TPAClaimStatusDate = @GetDate, UserLUP = 'PriceT', DateLUP = @GetDate
			where pkclaim = @pkClaim
		end
	
	-- PROCESS EXPENSE
	set @ClaimReserve_Expense =
	(select ReserveAmt from ClaimReserve Where fkClaim = @pkClaim and ReserveType = 'Exp' and History = 0)
	if @ClaimReserve_Expense <> 0
		Begin
			update claimreserve set History = 1 where fkClaim = @pkClaim and ReserveType = 'Exp' 
			insert into ClaimReserve (fkClaim, ReserveType, ReserveAmt, History, DateLup, UserLup, ChangeReason)
				        Values(@pkClaim,'Exp',@ReturnExpensePTD,0,@GetDate,'PriceT','Closing claim per policy.  System generated per xxxx.')	
			set @WriteNoteAndHistory = 1
			update claim set tpaclaimstatus = 'Closed', fkTPAClaimStatusReason = 15, TPAClaimStatusDate = @GetDate, UserLUP = 'PriceT', DateLUP = @GetDate
			where pkclaim = @pkClaim
		end
	
	-- PROCESS LEGAL
	set @ClaimReserve_Legal =
	(select ReserveAmt from ClaimReserve Where fkClaim = @pkClaim and ReserveType = 'legal' and History = 0)
	if @ClaimReserve_Legal <> 0
		Begin
			update claimreserve set History = 1 where fkClaim = @pkClaim and ReserveType = 'legal' 
			insert into ClaimReserve (fkClaim, ReserveType, ReserveAmt, History, DateLup, UserLup, ChangeReason)
				        Values(@pkClaim,'legal',@ReturnLegalPTD,0,@GetDate,'PriceT','Closing claim per policy.  System generated per xxxx.')	
			set @WriteNoteAndHistory = 1
			update claim set tpaclaimstatus = 'Closed', fkTPAClaimStatusReason = 15, TPAClaimStatusDate = @GetDate, UserLUP = 'PriceT', DateLUP = @GetDate
			where pkclaim = @pkClaim
		end
	
	-- PROCESS COMP
	set @ClaimReserve_Comp =
	(select ReserveAmt from ClaimReserve Where fkClaim = @pkClaim and ReserveType = 'Comp' and History = 0)
	if @ClaimReserve_Comp <> 0
		Begin
			update claimreserve set History = 1 where fkClaim = @pkClaim and ReserveType = 'Comp' 
			insert into ClaimReserve (fkClaim, ReserveType, ReserveAmt, History, DateLup, UserLup, ChangeReason)
				        Values(@pkClaim,'Comp',@ReturnCompPTD,0,@GetDate,'PriceT','Closing claim per policy.  System generated per xxxxx.')	
			set @WriteNoteAndHistory = 1
			update claim set tpaclaimstatus = 'Closed', fkTPAClaimStatusReason = 15, TPAClaimStatusDate = @GetDate, UserLUP = 'PriceT', DateLUP = @GetDate
			where pkclaim = @pkClaim
		end
	
	-- IF SOMETHING CHANGED WRITE HISTORY AND A NOTE
	if @WriteNoteAndHistory = 1
		begin
			
			set @MYfkParent = (Select fkLinkClaim from claim where pkclaim = @pkClaim)
			--Write a note
			exec [COMMONSQL].Notes.dbo.SaveNote
			@Action = 'XXXXX', -- Just use anything.  The SP doesn't care on insert 
			@Active = 1,
			@CreateDate = @GetDate,
			@CreateUserID = 'PriceT',
			@fkCategory = 1133,
			@fkParent = @MyfkParent,
			@Note = 'Manually reviewed by xxxxxx.  Closing in system at his request.',
			@fkParentLocation = 6710,  -- Production is 6710
			@pkNote = NULL,
			@Subject = 'Change in Reserves - Closing',
			@CreateDateOriginal = @GetDate,
			@CreateUserIDOriginal = 'PriceT',
			@UserLUP = 'PriceT',
			@DateLUP = @GetDate,
			@WarningNote = 0,
			@fkCapNote = null,
			@fkCompany = 5,
			@fkDepartment = 12,
			@CapMemoNumber = NULL,
			@HasAttachment = NULL,
			@HasWorkingOn = NULL,
			@Privilege = 37,
			@ReturnPkNote = NULL,
			@SelectReturn = NULL
			-- Write claim history	
			exec dbo.cst_WriteClaimHistory @pkClaim,1
		end
	
	FETCH NEXT FROM MyCursor INTO @pkClaim

end  

Close MyCursor
Deallocate MyCursor

 

[SQLSister]

First this is your problem in not being able to use a set-based solution:
exec cst_ClaimPTD

You need to write a new sp that will return all the results for all the claims in MedClaims_06_29_2007.

If this is terribly complicated and has many steps, you might need to loop through this sp and store the results in columns in MedClaims_06_29_2007 or a temp table. This would be a temporary solution until you figure out how to get this sp set-based.

Inthe mean time, once you have all these values, the rest seems fairly easy to do in a set based fashion. Something like this for the first part(Untested):

update cs 
set History = 1 
from claimreserve cs
join MedClaims_06_29_2007 m on cs.pkclaim = m.pkclaim

insert into ClaimReserve (fkClaim, ReserveType, ReserveAmt, History, DateLup, UserLup, ChangeReason)
select pkClaim,'Med',ReturnMedicalPTD,0,@GetDate,'PriceT','Closing claim per policy.  System generated per xxxx.' 
from  MedClaims_06_29_2007

update c
set tpaclaimstatus = 'Closed', 
fkTPAClaimStatusReason = 15, 
TPAClaimStatusDate = @GetDate, 
UserLUP = 'PriceT', 
DateLUP = @GetDate
from claim c
join MedClaims_06_29_2007 m on c.pkclaim = m.pkclaim

"NOTHING is more important in a database than integrity." ESquared

 

[TysonLPrice]

SQLSister,

I see what you are saying but there are two other pieces that cause the same difficulty as the SP cst_ClaimPTD. They are the call to SPs [COMMONSQL].Notes.dbo.SaveNote and cst_WriteClaimHistory. All three are fairly busy in themselves. Getting the data from cst_ClaimPTD into MedClaims_06_29_2007 is do-able. The other two don't, in my opinion, fit into the same work around. They are normally called from an application and update a number of tables.

They were easy to miss the way the code pasted in.

Thanks!

 

[SQLSister]

Probably the best approach is to work this one step at a time and make little bits set-based where you can and leave the other parts to run in the cursor if need be until you can get to looking at another way to write the code that forms the inputs. Like I said, taking the sps and putting their results in a temp table or extra columns in your work table may be the best work around for the moment. At least then you can use set-based on the main inserts and updates after you have done this and it should cut down onthe amount of time it takes although not improving it as much as a full set-based solution. Remeber, you do not need to reuse code if it makes perfomance bad. Write new procs to do this task and leave those alone for what they are doing in the user interface. User interfaces generally insert or update one record ata time. When you need to do this for more than one record, you need to look at differnt solutions or you end up withthe job that takes 24 hours to run on a production system withteh users complaining about timeouts becasue the system is busy. Better to design for multiple record inserts, updates and deletes from scratch than to reuse existing procs that operate one record at a time. Yes this means that two procs have to be maintained instead of one. But ease of maintenance should always take a back seat to performance and data integrity. Unfortunately in many shops it does not. This is one reason why there are so many poorly performing databases out there, becasue programmers were directed to do what was easiest and fastest rather than what would work correctly and efficiently.

"NOTHING is more important in a database than integrity." ESquared

 

[TysonLPrice]

Gotcha...

I did take three SPs from an interface that does one record at a time and plugged it into a loop.

It evolved from users waiting until the end of the quarter to close claims. The steps in the cursor are what they had to do on-line. A single request from one user to get them out of a bind with a backlog morphed into me doing it for everyone once a quarter.

It would be a real bear to rewrite it. This process runs once a quarter and at night so it hasn't raised any red flags. I do understand what you are saying. Maybe I can get my project lead to let me rewrite it correctly.

Thanks again!

 

[KDavie]

Can someone give me an example of how to accomplish what I am doing in my original thread with a set-based statement? I am looking on google trying to find some pointers, but am having no luck. My background is more on the applications side with a little tsql programming, so this is a daunting process for me.

Thanks,

-Kevin

Kevin Davie
Consultant
Sogeti USA

http://www.us.sogeti.com

 

[gmmastros]

There appears to be 2 tables involved in your cursor. Can you explain what this code is supposed to be doing. Then, some some sample records from both tables. Also, show what the expected results should be. It looks like you are updating data in table 1 and then deleting records from table 2.

I'm absolutely certain that someone here will be able to help you remove the cursor. The explanation I asked for will help us to help you.

-George

"the screen with the little boxes in the window." - Moron

 

[KDavie]

Basically, i am trying to select two columns from table2 (the primary key column and a name column) where the name column has a numerical value.

SELECT id, name FROM Table2 WHERE name NOT LIKE '%[^0-9]%'

The selected data represents bad data in the database. An application error was matching an id against the name and inserting the name when there wasn't already a match. So all those numerical values in the name column of the above select statement are actually the primary key of the correct record (hope this makes sense).

Table1 uses Table2 as a lookup table. There is a FK CONSTRAINT in table1 on the id field. So basically, I need to select all the rows from table1 that have a FK id equal to any of the id's in the above select statement. I then need to update the FK id in table1 to be the value of the name column for the corresponding row in table1 (are you still with me). Finally, I need to delete the bad row from table2.

Sample data:

Table2
id name
250 120
251 96
252 100

Table1
table2_id insert_date
250 01/01/07
251 02/01/07
252 03/01/07

Any help is appreciated.







Kevin Davie
Consultant
Sogeti USA

http://www.us.sogeti.com

 

[ESquared]

where the name column has a numerical value.

Just out of curiosity, what about a name that has numeric mixed with non-numeric, such as 'abc123'? If you don't care about those then you're fine, but if you do care about those then the query example you gave isn't right.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[KDavie]

Thanks... I only care about the rows that contain numerical data (explicitly).

Kevin Davie
Consultant
Sogeti USA

http://www.us.sogeti.com

 

[PDreyer]

I think this is what you want

begin transaction
update table1 set table1.table2_id=table2.name
from table1 
join table2 
  on  table1.table2_id=table2.id
  and table2.name not like '%[^0-9]%'
delete from table2 where table2.name not like '%[^0-9]%'
commit