The optimizer cannot use statistics the final select in the following procedure, because it cannot know the value of @city until execution time:
create procedure au_city_names
@pub_name varchar(30)
as
declare @city varchar(25)
select @city = city
from publishers where pub_name = @pub_name
select au_lname
from authors
where city = @city
The following example shows the procedure split into two procedures. The first procedure calls the second one:
create procedure au_names_proc
@pub_name varchar(30)
as
declare @city varchar(25)
select @city = city
from publishers
where pub_name = @pub_name
exec select_proc @city
create procedure select_proc @city varchar(25)
as
select au_lname
from authors
where city = @city
When the second procedure executes, Adaptive Server knows the value of @city and can optimize the select statement. Of course, if you modify the value of @city in the second procedure before it is used in the select statement, the optimizer may choose the wrong plan because it optimizes the query based on the value of @city at the start of the procedure. If @city has different values each time the second procedure is executed, leading to very different query plans, you may want to use with recompile.
