>Hi All,
>
>I am trying to create a Stored Procedure in SQL Server 2005. I ran the code in Management window to be sure that the code was correct. Everything runs fine there. I copy the needed code into my create stored procedure program and try to parse it. I get an error about 'incorrect syntax near bp_stu_autoid'.
>
>the only difference between my try and the SP is that I do not have the create tables code in there. The create tables is done before calling the SP. So that the temp tables will not disappear before they are needed in code.
>
>The code is 2nd in line of the same basic call.
>

>insert into #BUSPASS(bp_stu_autoid,PM_StopSrv_Idx,PM_stop_desc,PM_RunSrv_TimeAtSrv,
>   PM_RunSrv_Run_Idx,PM_Run_Desc,PM_Rte_ID,PM_Rte_BusNumber,PM_StopSrv_BellTime,
>   PM_school_desc, PM_StuTrip_FreqDisplay, PM_TripAsgn_DistToStop,
>   PM_StopSrv_Stop_Idx,PM_StopSrv_AsgnAvg, PM_StopSrv_HdcntAvg, PM_StuTrip_Desc,
>   PM_Rte_Desc,PM_Loc_Loc)
>  select * from #buspass2 where bp_stu_autoid not in(Select bp_stu_autoid from #buspass1 )  && this is the line
>

>
>This is the line that the complier does not like, as far as I can tell.
>
>TIA
>Beth

First, I would change this:

 select * from #buspass2 where bp_stu_autoid not in(Select bp_stu_autoid from #buspass1 )

to

 select #buspass2.* from #buspass2
     LEFT JOIN #buspass1 ON #buspass2.bp_stu_autoid = #buspass1.bp_stu_autoid
 WHERE #buspass1.bp_stu_autoid IS NULL

just because of speed.

Second When you create that SP all temp tables must be created. Something like:

CREATE #BUSPASS(bp_stu_autoid,PM_StopSrv_Idx,PM_stop_desc,PM_RunSrv_TimeAtSrv,
                PM_RunSrv_Run_Idx,PM_Run_Desc,PM_Rte_ID,PM_Rte_BusNumber,PM_StopSrv_BellTime,
                PM_school_desc, PM_StuTrip_FreqDisplay, PM_TripAsgn_DistToStop,
                PM_StopSrv_Stop_Idx,PM_StopSrv_AsgnAvg, PM_StopSrv_HdcntAvg, PM_StuTrip_Desc,
                PM_Rte_Desc,PM_Loc_Loc) -- add the field types and length here, I am too lazy to do so :-)


CREATE #BUSPASS2(bp_stu_autoid,PM_StopSrv_Idx,PM_stop_desc,PM_RunSrv_TimeAtSrv,
                PM_RunSrv_Run_Idx,PM_Run_Desc,PM_Rte_ID,PM_Rte_BusNumber,PM_StopSrv_BellTime,
                PM_school_desc, PM_StuTrip_FreqDisplay, PM_TripAsgn_DistToStop,
                PM_StopSrv_Stop_Idx,PM_StopSrv_AsgnAvg, PM_StopSrv_HdcntAvg, PM_StuTrip_Desc,
                PM_Rte_Desc,PM_Loc_Loc) -- The same

CREATE #BUSPASS1(bp_stu_autoid int)
GO
CREATE PROCEDURE ......
AS
....