ODBC Applications: Writing Good Code - The Data Access - page 138

Example B: Using a Server-Side RPC
The stored procedure getCustName is optimized to use a server-side
RPC. Because the application avoids literal arguments and calls the pro-
cedure by specifying arguments as parameters, the driver optimizes the
execution by invoking the stored procedure directly on the database as
an RPC. The SQL language processing by the database is avoided, and
execution time is faster.
strcpy (sqlStatement,"{call getCustName (?)}");
rc = SQLPrepare((SQLHSTMT)hstmt, sqlStatement, SQL_NTS);
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
SQL_C_LONG, SQL_INTEGER, 10, 0,
&id, sizeof(id), NULL);
id = 12345;
rc = SQLExecute(hstmt);
Why doesn't the driver parse and automatically change the SQL stored pro-
cedure call when it encounters a literal argument so that it can execute the stored
procedure using an RPC? Consider this example:
{call getCustname (12345)}
The driver doesn't know if the value 12345 represents an integer, a decimal, a
smallint, a bigint, or another numeric data type. To determine the correct data
type for packaging the RPC request, the driver must make an expensive network
round trip to the database server. The overhead needed to determine the true
data type of the literal argument far outweighs the benefit of trying to execute the
request as an RPC.
Using Statements Versus Prepared Statements
Most applications have a certain set of SQL statements that are executed multiple
times and a few SQL statements that are executed only once or twice during the
life of the application. Choose the SQLExecDirect function or the
SQLPrepare / SQLExecute functions depending on how frequently you plan to
execute the SQL statement.