Use method 2, prepare and execute, when one of the following cases is true:
You are certain that no data will be retrieved, and you want the statement to execute more than once.
A select statement is to return a single row. With this method, you cannot associate a cursor with the select statement.
This process is also called a single-row select. If a user needs to retrieve multiple rows, use method 3 or 4.
This method uses prepare and execute to substitute data from C variables into a Transact-SQL statement before sending the statement to Adaptive Server. The Transact-SQL statement is stored in a character buffer with dynamic parameter markers to show where to substitute values from C variables.
Because this statement is prepared, Adaptive Server compiles and saves it as a temporary stored procedure. Then, the statement executes repeatedly, as needed, during the session.
The prepare statement associates the buffer with a statement name and prepares the statement for execution. The execute statement substitutes values from a list of C variables or SQL descriptors into the buffer and sends the completed statement to Adaptive Server. You can execute any Transact-SQL statement this way.
