not sure what you mean but this seems to be working just fine

execute ("Select * from #treport order by 79, " + @lcsortno)

so if I am not mistaking, the # means the local temp table you're referring to

>>Hi I am trying to modify a Stored procedure that returns a result set at the end with
>>
>>execute ("select * from #table")
>>
>>
>>to this i would like to join another table that already exist on the database called apvadr
>>
>>lets say that #table has these columns
>>code, item, descr, qty
>>1 1 1 1
>>2 2 2 2
>>
>>and apvadr has this struc
>>cvendno caddr1 caddr2
>>3 3 3
>>
>>
>>i am trying to get this result
>>cvendno caddr1 caddr2 code, item, descr, qty
>>3 3 3 1 1 1 1
>>3 3 3 2 2 2 2
>>
>>
>>is it possible or i am just making a mistake?
>>
>>this is the actual script
>>
>>
>>select @lcsqlcmd = "select *, apvadr.cphone as cBphone, apvadr.ccontact as cBcontact, " +
>> "apvadr.ccompany as cBcompany, apvadr.caddr1 as cBaddr1, apvadr.caddr2 as cBaddr2,"+
>> "apvadr.ccity as cBcity, apvadr.cstate as cBstate, apvadr.czip as cBzip, " +
>> "apvadr.ccountry as cBcountry " +
>> "from #treport, apvadr " +
>> "were apvadr.cvendno = 'Z_SKIPS' and apvadr.ctype='O' and apvadr.caddrno = 'MIA' " +
>> "order by 79, " + @lcsortno
>>
>>execute (@lcsqlcmd)
>>
>>
>>as I mentioned before #treport is created on the SP, apvadr already exists on the database
>
>AFAIK your local temp table would not be visible for EXECUTE.
>
>Do you need to use separate batch (execute) because of the conditional ORDER BY clause?