CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2

Hybrid View

  1. #1
    Join Date
    Aug 2004
    Location
    Land of sunshine and June Gloom
    Posts
    171

    Angry 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.

  2. #2
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    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
  •  





Click Here to Expand Forum to Full Width

Featured