>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?