Could not complete cursor operation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Could not complete cursor operation

Author

Topic

hearnie
Starting Member

49 Posts

Posted - 2005-04-11 : 08:48:54

......because table schema changed after the cursor was declared

This is an error I am getting when running a stored procedure of mine that selects rows from a temporary table and searches a table in the database to see if the row exists or not. If it does it updates it, if it doesnt it should insert it.

Does anyone have any suggestions on what is causing this?

cheers

H.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-11 : 08:54:37

don't use cursors.

can you show us the code that gives the error?

Go with the flow & have fun! Else fight the flow

hearnie
Starting Member

49 Posts

Posted - 2005-04-11 : 09:57:07

Everyone on this site seems to say dont use cursors.....

I get the message, but cant you understand that Im looking for solutions and not alternatives!.....dont mind me, just been stuck on this prob too long.

Im probably doing this all wrong and its a bit pseudo code 'ish but any pointers or help would be greatly appreciated

CREATE PROCEDURE spInkingTruFormat AS
DECLARE
.........all my vars here

DECLARE myDatacursor CURSOR FOR
SELECT field1,field2,field3,field4 from TempDataTable1

OPEN myDatacursor

FETCH FROM myDatacursor INTO @var1,@var2,@var3,@var4
'did this part because I thought it might have been somehow getting affected and screwing things up
set @fetchObj = @@FETCH_STATUS
WHILE (@fetchObj <> -1)
BEGIN

--Another TempDataTable that I need to get values from
IF EXISTS (SELECT * FROM TempDataTable2 WHERE field1=@lot and field2=@var2)
BEGIN
SELECT @match = (SELECT product FROM TempInterfaceData WHERE sum=@var3 and total=@var4)

END

IF NOT EXISTS (SELECT * FROM MyDataTable WHERE col1= @var1 and col2=@var2)
BEGIN
--do the insertions
INSERT INTO MyDataTable (appname,description)
VALUES (@var3,@var4)

IF @@ERROR <> 0
BEGIN
RAISERROR('ERROR INSERTING',16,1)
END

--get the id of identity as needed for insert
select @my_id = @@identity

END
ELSE
BEGIN
IF EXISTS (SELECT * FROM MyDataTable WHERE col1= @var1 and col2=@var2)
BEGIN
UPDATE MyDataTable
SET appname = @var3, description=@var4
WHERE col1= @var1 and col2=@var2

IF @@ERROR <> 0
BEGIN
RAISERROR('ERROR UPDATING',16,1)
END

END

END

--this section handles the insertion or updates to the second table which is linked to MyDataTable by a Foreign Key
IF NOT EXISTS (SELECT * FROM MyDataTable2 WHERE field_id=@my_id)
BEGIN
INSERT INTO MyDataTable2 (....fields)
VALUES (...@values)

IF @@ERROR <> 0
BEGIN
RAISERROR('ERROR INSERTING',16,1)
END

END
ELSE
BEGIN
UPDATE MyDataTable2
SET sum=@var2
WHERE field_id=@my_id and col3=@var3

IF @@ERROR <> 0
BEGIN
RAISERROR('ERROR UPDATING',16,1)
END

END

FETCH NEXT FROM curInkingData INTO @var1,@var2,@var3,@var4

set @fetchObj = @@FETCH_STATUS
END

CLOSE MyDatacursor
DEALLOCATE MyDatacursor
GO

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-11 : 11:17:37

Please try to keep it all in one thread, as this is basically the same issue as in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48256

-------
Moo. :)

hearnie
Starting Member

49 Posts

Posted - 2005-04-11 : 11:27:31

well it is and it isnt....the other one is ye all trying to convert me to set theory

...while this one was trying to find out what that damn error is when running the stored procedure.

but I get your meaning

H.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources