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

Performance Tip
Call stored procedures by invoking an RPC with parameter markers for
arguments instead of using literal arguments. Because the database skips
the parsing and optimization required in executing the stored procedure
as a SQL statement, performance is significantly improved.
Remember that SQL is always sent to the database as a character string. For
example, consider the following stored procedure call, which passes a literal
argument to the stored procedure:
{call getCustName (12345)}
Although the argument to getCustName() is an integer, the argument is
passed inside a character string to the database, namely {call getCustName
(12345)} . The database parses the SQL statement, isolates the single argument
value of 12345, and converts the string 12345 into an integer value before execut-
ing the procedure as a SQL language event. Using an RPC on the database, your
application can pass the parameters to the RPC. The driver sends a database pro-
tocol packet that contains the parameters in their native data type formats, skip-
ping the parsing and optimization required to execute the stored procedure as a
SQL statement. Compare the following examples.
Example A: Not Using a Server-Side RPC
The stored procedure getCustName is not optimized to use a server-side
RPC. The database treats the SQL stored procedure execution request as
a normal SQL language event, which includes parsing the statement,
validating argument types, and converting arguments into the correct
data types before executing the procedure.
strcpy (sqlStatement,"{call getCustName (12345)}");
rc = SQLPrepare((SQLHSTMT)hstmt, sqlStatement, SQL_NTS);
rc = SQLExecute(hstmt);