Method 3 uses the prepare statement with cursor statements to return results from a select statement. Use this method for fixed-list select statements that may return multiple rows. That is, use it when the application has determined in advance the number and type of select column list attributes to be returned. You must anticipate and define host variables to accommodate the results.
When you use method 3, include the declare, open, fetch, and close cursor statements to execute the statement. This method is required because the statement returns more than one row. There is an association between the prepared statement identifier and the specified cursor name. You can also include update and delete where current of cursor statements.
As with method 2, a Transact-SQL select statement is first stored in a character host variable or string. It can contain dynamic parameter markers to show where to substitute values from input variables. The statement is given a name to identify it in the prepare, declare, and open statements.
Method 3 requires five steps:
prepare
declare
open
fetch (and, optionally, update and delete)
close
These steps are described below.
