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

>Hi Borislav,
>I will try your select statement. The Tables are created, from vfp, just before the calling of the SP, because the tables need to be there beyond the SP. I am just trying to build the SP so that I can call it from vfp now.
>
>Thanks,
>Beth

No matter, run this in QA or SSMS just to create SP, then you could DROP Tables and use that SP from everywhere you need it. You don't have to fill the tables. Just don't forget GO before CREATE PROCEDURE statement. I am not sure right now if CREATE PROCEDURE must be the first line in the batch, but just in case :-).