Why won't following stored procedure work?

[JetDeveloper]

Hi,

Here is the stored procedure (MSSQL 2005)

create procedure [dbo].[NotWorking] (@wellchemlist_xml ntext)
as

create table #selwell (well_id int)
-- Load parameter into an XML document
DECLARE @xmldoc int
exec sp_xml_preparedocument @xmldoc OUTPUT, @wellchemlist_xml

INSERT INTO #selwell (well_id)
SELECT wid FROM OPENXML (@xmldoc, '/D/WL/W', 1)
WITH (wid int)

-- Execute the select
select chm.chem_id, chm.cas, chm.chem_name, chm.units
from chemical chm inner join #selwell w ON w.well_id = cd.well_id
inner join chemical_data cd on (chm.chem_id = cd.chem_id)
order by chm.chem_id

-- Free the XML document from memory
EXEC sp_xml_removedocument @xmldoc

When I run it like this (well_id is a valid column name in chemical_data)

exec notworking '<D><WL><W wid="3" /></WL></D>'

It is giving the following error:

Msg 4104, Level 16, State 1, Procedure NotWorking, Line 14
The multi-part identifier "cd.well_id" could not be bound.


Do you know why this error is happening?

Thanks in advance

[Shuja Ali]

Well your Select query doesn't look right. Try this

select 
	chm.chem_id, chm.cas, chm.chem_name, chm.units
from 
	chemical chm 
	inner join chemical_data cd 
	on chm.chem_id = cd.chem_id
	inner join #selwell w 
	ON w.well_id = cd.well_id
order by 
	chm.chem_id

Look at how I changed the order of the joins. You were trying to refer to a column which is a from a table that was not part of the joins before the Inner Join with #selwell.