October 17th, 2009, 01: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 01:27 AM.
October 17th, 2009, 03: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
Use [code]your code here[/code] tags when you post source code
Search here before you post your question, someone might have already asked it before.
Tags for this Thread
Click Here to Expand Forum to Full Width