Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: Multi-table Select Stored Proc - Problem

    Hi Everyone,
    I'm having trouble querying a couple of tables and want to return all records from each with results for the selects in separate row sets. I'm able to get all of one table records but the second table I get an error. I'm lost at this point at what to do.

    Msg 4104, Level 16, State 1, The multi-part identifier error for all it's records could not be bound.

    Here is what I have:
    Code:
     ALTER PROCEDURE [dbo].[spReportTest]
    (
        @Param1 NVARCHAR(7)
    )
    AS
    BEGIN
    
    /****** Tempt table to join to tblDds table *************/
    
    SELECT dbo.tblWin.ID AS MYID, dbo.tblWin.Rname AS MYNAME INTO #tblUsers 
    FROM dbo.tblWin 
    WHERE dbo.tblWin.Rname = @Param1;
    
    SET NOCOUNT ON;
    
    /******* This table returns all records as expected ********/
    
    SELECT			dbo.tblWin.ID, dbo.tblWin.Rname, 
    			dbo.tblWin.MCVEC AS VHICLE, dbo.tblWin.MCPOR AS PORTABLE, 
    			dbo.tblWin.MCAIR AS AIRCRAFT, dbo.tblWin.MCMAR AS MARINE, 
    			dbo.tblWin.MCPAG AS PAGER, dbo.tblWin.ORCOU AS COUNTY, 
    			dbo.tblWin.ORSTA AS STATE, dbo.tblWin.OROTH AS OTHER, 
    			dbo.tblWin.ELIG1 AS ELIGIBILITY1, dbo.tblWin.ELIG2 AS ELIGIBILITY2, 
    			dbo.tblWin.ELIG3 AS ELIGIBILITY3, dbo.tblWin.ELIGR AS ELIGIBILITY_RULE, 
    			dbo.tblWin.SPIT1 AS SPEC1, dbo.tblWin.SPIT2 AS SPEC2
    FROM	dbo.tblWin 
    WHERE 	dbo.tblWin.Rname = @Param1
    
    /******* This table is multipart error no actual records returned ********/
    SELECT       dbo.tblDds.ID, dbo.tblDds.LCODE, dbo.tblDds.LOCOD AS LOCCODE
    FROM         dbo.tblDds INNER JOIN
                 dbo.#tblUsers ON dbo.tblDds.ID = dbo.#tblUsers.MYID
    
    DROP TABLE #tblUsers
    END
    Last edited by SQLRookie; 10-11-11 at 15:38. Reason: fixed a field, still not working

  2. #2
    Join Date
    Oct 2011
    Posts
    3

    Re: Multi-table Select Stored Proc - Problem solved

    Hi Everyone,
    I solved this incremental piece of the SP.
    I moved SET NOCOUNT ON to above the temp table code.
    And now this part works.

    I have a grouping problem now
    Thanks
    SR

  3. #3
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    do we really need a temp table in this stored proc?


    why dont u rewrite the 2nd query as below
    SELECT dbo.tblDds.ID,
    dbo.tblDds.LCODE,
    dbo.tblDds.LOCOD AS LOCCODE
    FROM dbo.tblDds DD
    INNER JOIN dbo.tblWin Win
    ON dbo.DD.ID = Win.ID
    WHERE Win.Rname = @Param1;


    hope this will work
    Cheers....

    baburajv

  4. #4
    Join Date
    Oct 2011
    Posts
    3

    Re: Multi-table Select Stored Proc - Problem

    Hi baburajv,
    Thank you for reply and code, but I get same multipart error. I don't know if it matters using SQLs2005. This is my first SP and a difficult one for me. I only posted a small peice. it has 5-selects and I need to group the results, which I have no idea how to do. If you know some example please forward?
    Thanks
    -Cebo


    Quote Originally Posted by baburajv View Post
    do we really need a temp table in this stored proc?


    why dont u rewrite the 2nd query as below
    SELECT dbo.tblDds.ID,
    dbo.tblDds.LCODE,
    dbo.tblDds.LOCOD AS LOCCODE
    FROM dbo.tblDds DD
    INNER JOIN dbo.tblWin Win
    ON dbo.DD.ID = Win.ID
    WHERE Win.Rname = @Param1;


    hope this will work

  5. #5
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    could you please post the complete SP code?
    Cheers....

    baburajv

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
  •