Dynamic SQL versus stored procedures

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:

Stored procedures and dynamic SQL prepared statements offer identical functionality, with the following exceptions:

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.