CODEWITHSUNDEEP
sql-servert-sqldb2cursor
Farhan
Farhan

Reputation: 2575

Is this DB2 cursor a loop?

Through fate or luck, I am working on converting DB2 stored procedures to SQL Server stored procedures.

One thing I could not completely understand in DB2 is cursors1. By looking at it and reading some documentation, it appears that it is only a select statement.

-- DB2 stored procedure code
declare entity_cursor cursor with return for
select *
from TableName;

...
--a lot more cursors like above, and some other code
...

open entity_cursor;

Questions

  1. Am I correct in my assumption2 that this is only a select statement actually returning a result set when the cursor is opened?
  2. If an error has occurred before the cursor is opened3, will the SP return a blank result set for entity_cursor?

1: I know that SQL Server they are commonly used for performing operations on row-by-row.
2: Based on reading DB2 documentation and a little on my knowledge and intellect.
3: Unfortunately I cannot fully test the stored procedure in DB2.

Upvotes: 2

Views: 1440

Answers (1)

mustaccio
mustaccio

Reputation: 18945

To your first question: as mentioned in comments, opening a cursor does not produce a result set, but merely a (pointer to a) structure allowing you to access the result set using the FETCH statement or equivalent. Moreover, the result set may not even be fully materialized when you start fetching records -- this depends on the actual query and various cursor options.

To your second question: if your stored procedure returns, normally or otherwise, before opening the cursor, its caller will get an uninitialized cursor structure (which will cause an exception when you try to access it) and not an empty result set.

Upvotes: 1

Related Questions