Dynamic SQL is the process of generating, preparing, and executing SQL statements at run time using commands initiated by Client-Library’s ct_dynamic routine.
Dynamic SQL is primarily useful for precompiler support, but it can also be used by interactive applications.
Client-Library and Adaptive Server Enterprise allow two methods of dynamic SQL command execution:
Execute-immediate – the client application sends the server a ct_dynamic command that executes a literal statement. This is essentially the same process as sending a language command, but with more restrictions. (See “Language commands”.)
Prepare-and-execute – the client application sends the server a sequence of server commands that prepares a statement and executes it one or more times. The application can send additional commands to query the server for the formats of the statement’s input parameters and the result set that it returns.
With the prepare-and-execute method, the client application sends a ct_dynamic(CS_PREPARE) command to the server to create a prepared statement. A prepared statement is similar to an Adaptive Server Enterprise stored procedure. When either is created, the server checks the SQL statement syntax, builds an optimized query plan, and stores the query plan in preparation for later execution. The key differences are as follows:
The prepared statement is dropped automatically when the client program disconnects, while the stored procedure is not.
The prepared statement is referenced by an identifier that is visible only to the connection that created the statement, while a stored procedure name is visible to any client connection. However, the procedure’s permissions may restrict which users can execute it.
The client program can easily determine the input (parameter) and output (result) column formats for a prepared statement without executing it.
