For improved performance compared to dynamic SQL, application designers can use Adaptive Server Enterprise stored procedures as an alternative where the application requirements allow it.
There are similarities between dynamic SQL and stored procedures:
Creating a stored procedure is analogous to preparing a dynamic SQL statement.
A stored procedure’s input parameters serve the same purpose as dynamic parameter markers.
Executing a stored procedure is equivalent to executing a prepared statement.
Stored procedures and dynamic SQL prepared statements offer identical functionality, with the following exceptions:
Dynamic SQL allows retrieval of a prepared statement’s parameter formats, while stored procedures do not. See “Step 2: Get a description of command inputs”.
The format for stored procedure results cannot easily be determined programmatically without executing the procedure. Dynamic SQL allows retrieval of a prepared statement’s result column formats without executing the statement. See “Step 3: Get a description of command outputs”.
User-created stored procedures are permanent database objects, while prepared statements are automatically deallocated when the user disconnects from the server.
A dynamic SQL statement can be replaced by a stored procedure that returns the same results. For example, the following dynamic SQL statement queries the pubs2..titles table for books of a certain type in a certain price range:
select * from pubs2..titles
where type = ?
and price between ? and ?
Here, the dynamic SQL statement has dynamic parameter markers (?) for a type value and two price values.
You can create an equivalent stored procedure as follows:
create proc titles_type_pricerange
@type char(12),
@price1 money,
@price2 money
as
select * from titles
where
type = @type
and price between @price1 and @price2
When executed with the same input parameter values, the prepared statement and the stored procedure return the same rows. In addition, the stored procedure returns a return status result.
