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.