October 17th, 2009, 12:22 AM
Why won't following stored procedure work?
Here is the stored procedure (MSSQL 2005)
When I run it like this (well_id is a valid column name in chemical_data)
create procedure [dbo].[NotWorking] (@wellchemlist_xml ntext)
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
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
Last edited by JetDeveloper; October 17th, 2009 at 12:27 AM.
October 17th, 2009, 02:28 PM
Re: Why won't following stored procedure work?
Well your Select query doesn't look right. Try this
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.
chm.chem_id, chm.cas, chm.chem_name, chm.units
inner join chemical_data cd
on chm.chem_id = cd.chem_id
inner join #selwell w
ON w.well_id = cd.well_id
Tags for this Thread
Click Here to Expand Forum to Full Width