• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3500
  • Last Modified:

Sql server sproc error - "the multi-part identifier could not be bound"

The sql 2005 sproc below is used in my asp.net c# app

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[aspnet_ExtendedMembership_GetUsersByUserIds]
-- Procedure used to gell all users whose Ids are
-- listed within the input list
(
      @ListOfUserIds VARCHAR(500)
)
AS
BEGIN
      DECLARE @SqlQuery VARCHAR(600)

      -- Prepare the query to retreive all user records based on the list of UserIds
      SELECT @SqlQuery =
            'SELECT     aspnet_ExtendedMembership_UserInfo.UserId, aspnet_ExtendedMembership_UserInfo.FirstName, aspnet_ExtendedMembership_UserInfo.LastName,
                      aspnet_ExtendedMembership_UserInfo.DateOfBirth, aspnet_ExtendedMembership_UserInfo.CominoID,
                      aspnet_ExtendedMembership_UserInfo.OracleID, aspnet_ExtendedMembership_UserInfo.CCDID, aspnet_ExtendedMembership_UserInfo.TeamID,
                      aspnet_ExtendedMembership_UserInfo.DeptID, aspnet_ExtendedMembership_UserInfo.PrincipalOfficerID, Department.DeptName,
                      aspnet_ExtendedMembership_UserInfo.MembershipFilter, aspnet_ExtendedMembership_UserInfo_1.UserId AS TeamID,
                      aspnet_ExtendedMembership_UserInfo_1.FirstName + '' '' + aspnet_ExtendedMembership_UserInfo_1.LastName AS TeamLeaderName,                      
                      aspnet_ExtendedMembership_UserInfo_2.UserId AS PrincipalOfficerID,
                      aspnet_ExtendedMembership_UserInfo_2.FirstName + '' '' + aspnet_ExtendedMembership_UserInfo_2.LastName  AS PrincipalOfficerName                      
            FROM         aspnet_ExtendedMembership_UserInfo INNER JOIN
                      Department ON aspnet_ExtendedMembership_UserInfo.DeptID = Department.DeptID INNER JOIN
                      aspnet_ExtendedMembership_UserInfo AS aspnet_ExtendedMembership_UserInfo_1 ON
                      aspnet_ExtendedMembership_UserInfo.TeamID = aspnet_ExtendedMembership_UserInfo_1.UserId INNER JOIN
                      aspnet_ExtendedMembership_UserInfo AS aspnet_ExtendedMembership_UserInfo_2 ON
                      aspnet_ExtendedMembership_UserInfo.PrincipalOfficerID = aspnet_ExtendedMembership_UserInfo_2.UserId
             WHERE aspnet_ExtendedMembership_UserInfo.UserId IN
(' + @ListOfUserIds + ')'

      -- Execute the above query
      EXEC(@SqlQuery)      
END

===

The following error is thrown in the app when I execute the sproc

 Message="The multi-part identifier \"aspnet_ExtendedMembership_UserInfo.UserId\" could not be bound.\r\nThe multi-part identifier \"aspnet_ExtendedMembership_UserInfo.FirstName\" could not be bound.\r\nThe multi-part identifier \"aspnet_ExtendedMembership_UserInfo.LastName\" could not be bound.\r\nThe multi-part identifier \"aspnet_ExtendedMembership_UserInfo.DateOfBirth\" could not be bound.\r\nThe multi-part identifier \"aspnet_ExtendedMembership_UserInfo.CominoID\" could not be bound.\r\nThe multi-part identifier \"aspnet_ExtendedMembership_UserInfo.OracleID\" could not be bound.\r\nThe multi-part identifier \"aspnet_ExtendedMembership_UserInfo.CCDID\" could not be bound.\r\nThe multi-part identifier \"aspnet_ExtendedMembership_UserInfo.TeamID\" could not be bound.\r\nThe multi-part identifier \"aspnet_ExtendedMembership_UserInfo.DeptID\" could not be bound.\r\nThe multi-part identifier \"aspnet_ExtendedMembership_UserInfo.PrincipalOfficerID\" could not be bound.\r\nThe multi-part identifier \"Department.DeptName\" could not be bound.\r\nInvalid column name 'aspnet_ExtendedMe'."
==========================

What is wrong with this query?!!











0
mugsey
Asked:
mugsey
1 Solution
 
NBSO_ISSCommented:
Looks to me like your @SqlQuery VARCHAR(600) may not be long enough.  As a test, change the EXEC(@SqlQuery)      to Select @SqlQuery and run it in locally so you can see the query returned.
0
 
mugseyAuthor Commented:
Genius!!  Thanks a lot!!!!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please note that your sql does not need to by dynamic at all.

create the following function:

CREATE FUNCTION dbo.ParmsToList (@Parameters varchar(500))
returns @result TABLE (Value varchar(30))
AS  
begin
     DECLARE @TempList table
          (
          Value varchar(30)
          )

     DECLARE @Value varchar(30), @Pos int

     SET @Parameters = LTRIM(RTRIM(@Parameters))+ ','
     SET @Pos = CHARINDEX(',', @Parameters, 1)

     IF REPLACE(@Parameters, ',', '') <> ''
     BEGIN
          WHILE @Pos > 0
          BEGIN
               SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)
               SET @Pos = CHARINDEX(',', @Parameters, 1)

          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END    



and your procedure will change like this:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[aspnet_ExtendedMembership_GetUsersByUserIds]
-- Procedure used to gell all users whose Ids are
-- listed within the input list
(
      @ListOfUserIds VARCHAR(500)
)
AS
BEGIN
SELECT     ui.UserId
  , ui.FirstName
  , ui.LastName
  , ui.DateOfBirth
  , ui.CominoID
  , ui.OracleID
  , ui.CCDID
  , ui.TeamID
  , ui.DeptID
  , ui.PrincipalOfficerID
  , d.DeptName
  , ui.MembershipFilter
  , ui1.FirstName + ' ' + ui1.LastName AS TeamLeaderName
  , ui2.FirstName + ' ' + ui2.LastName AS PrincipalOfficerName                      
FROM  aspnet_ExtendedMembership_UserInfo ui
INNER JOIN Department d
  ON ui.DeptID = d.DeptID
INNER JOIN aspnet_ExtendedMembership_UserInfo AS ui1
  ON  ui.TeamID = ui1.UserId
INNER JOIN aspnet_ExtendedMembership_UserInfo AS ui2
  ON  ui.PrincipalOfficerID = ui2.UserId
WHERE ui.UserId IN ( select value from dbo.ParmsToList(@ListOfUserIds))

END


note that I changed to use table alias names a bit more efficiently,
and I removed the 2 columns which are duplicates...
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now