-
October 17th, 2009, 12:22 AM
#1
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
Last edited by JetDeveloper; October 17th, 2009 at 12:27 AM.
-
October 17th, 2009, 02:28 PM
#2
Re: Why won't following stored procedure work?
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.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|