Why won't following stored procedure work?

Hi,

Here is the stored procedure (MSSQL 2005)

Code:

---

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)

Code:

---

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

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

Code:

---

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.