CODEWITHSUNDEEP
sqlsql-servert-sql
Nate Pet
Nate Pet

Reputation: 46222

SQL Server CURSOR not working

Need a cursor to update a value from another stored procure and updates only selected values

I have the following:

DECLARE upd_cursor CURSOR FOR
SELECT * FROM Terr
WHERE Text = "RightT" ;

OPEN upd_cursor;

-- Perform the first fetch.
FETCH NEXT FROM upd_cursor;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

 exec usp_Gent  @valback OUTPUT; 
 update Terr  
 set Text = @valback

FETCH NEXT FROM upd_cursor;
END

CLOSE upd_cursor;
DEALLOCATE upd_cursor;
GO

In my case, it updates all of the field in the table. What am I doing wrong

Upvotes: 1

Views: 2663

Answers (3)

Conrad Frix
Conrad Frix

Reputation: 52645

If you really wanted to do a row by row update you could use CURRENT OF syntax. This would be the case if your call to the stored procedure returned different results on each.

e.g.

     update Terr  
     set Text = @valback
     WHERE CURRENT OF upd_cursor

Upvotes: 1

Eric
Eric

Reputation: 95123

You have no where clause on your update statement.

More generically, you should use a single update clause to do this rather than a cursor:

exec usp_Gent @valback OUTPUT;
update Terr set
    Text = @valback
where
    Text = 'RightT';

Unless you're usp_Gent is doing something that I'm missing.

The reason you want a single clause instead of doing one for each row is because SQL thinks better in sets. Each update you have has overhead--it opens up a transaction, records what it's doing, and then commits that transaction. This is remarkably slow if you have to do thousands of these. What you really want to do is just update thousands of row at once--this is what databases do with relational algebra, and they are really, really good at doing it. Think in sets, not in order.

Upvotes: 3

Yuck
Yuck

Reputation: 50835

update Terr  
set Text = @valback

You aren't setting a WHERE condition in this statement.

Also, why are you using cursors?

Upvotes: 3

Related Questions