Splitting stored procedures to improve costing

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.