Adaptive Server Enterprise implements dynamic SQL using temporary stored procedures. A temporary stored procedure is created when a SQL statement is prepared, and destroyed when that prepared statement is deallocated. A prepared statement can be deallocated either explicitly with a ct_dynamic(CS_DEALLOC) call or implicitly when a connection is closed.
As a consequence of this implementation, an application accessing Adaptive Server Enterprise and using dynamic SQL is subject to the restrictions of Adaptive Server Enterprise stored procedures. Some of the implications of this are:
Temporary tables are destroyed when the prepared statement is deallocated.
Parameters of text and image datatypes are not supported.
The maximum number of parameters supported is 255.
If the dynamic SQL statement itself executes a stored procedure (with a Transact-SQL execute statement), output parameter values and the return status are unavailable to the client application.
The datatype of the parameters represented by placeholders must be known at parsing time. The following statements are not valid:
? <op> ?, (? is null)
CONVERT(<type>, ?)
See the Transact-SQL Users Guide for a complete discussion of stored procedures.
