Sql-server – The multi-part identifier “xxx” could not be bound

sql serversql-server-2012stored-procedures

The following is a HAVING clause from a small part of a very large stored procedure which I have inherited from some other devs (external);

HAVING (SELECT COUNT(*) FROM
(
    SELECT *
    FROM dbo.ContractDailyRoomAllocation da2
    WHERE da2.ContractId = DA.ContractId
    AND da2.RoomTypeId = DA.RoomTypeId
    AND da2.Date >= @FromDate AND da2.Date < @ToDate
    AND da2.IsSold = 0
    AND da2.ReleaseDay <= DATEDIFF("d", @TodayDate, da2.Date)) T) = @StayDates
    AND ( (@AllowOnRequestBookings = 'False' 
      OR dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate)  
      >= 0 ) )
      AND ( (@AllowOnRequestBookings = 'True' 
      OR dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate) 
      > 0 )
)

The last 2 AND's give me the following error messages:

Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 176
The multi-part identifier "da2.ContractId" could not be bound.
Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 176
The multi-part identifier "da2.RoomTypeId" could not be bound.
Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 177
The multi-part identifier "da2.ContractId" could not be bound.
Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 177
The multi-part identifier "da2.RoomTypeId" could not be bound.

Any ideas?

Best Answer

Reformatting your code shows the problem. All I have changed is whitespace and added a comment:

HAVING
    (
        SELECT COUNT(*) FROM
        (
            SELECT *
            FROM dbo.ContractDailyRoomAllocation da2 -- <-- da2 defined here
            WHERE da2.ContractId = DA.ContractId
            AND da2.RoomTypeId = DA.RoomTypeId
            AND da2.Date >= @FromDate AND da2.Date < @ToDate
            AND da2.IsSold = 0
            AND da2.ReleaseDay <= DATEDIFF("d", @TodayDate, da2.Date)
        ) T
    ) = @StayDates
AND
    ( 
        (
            @AllowOnRequestBookings = 'False' OR
            dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate) >= 0

        )
    )
AND 
    (
        (
            @AllowOnRequestBookings = 'True' OR
            dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate) > 0
        )
    )

As can now easily be seen, da2 is an alias defined in the subquery in the first ANDd term, and so is not available in other terms.

It may be that you want to move the second and third ANDd terms here into the subquery, or it might be more complicated than that.

Related Solutions

Sql-server – Statement could not be prepared

There are a couple JOIN combinations that seem abnormal to me.

For example:

left outer join dbo.vwQuoteStatus 
     inner join appdb.ABC_LABS_MSCRM.dbo.QuoteExtensionBase qeb 
             on dbo.vwQuoteStatus.AttributeValue = qeb.ABC_QuoteStatus

vs.

left outer join dbo.vwQuoteStatus 
                appdb.ABC_LABS_MSCRM.dbo.QuoteExtensionBase qeb 
             on dbo.vwQuoteStatus.AttributeValue = qeb.ABC_QuoteStatus

Sql-server – Query intermittently fails with “The multi-part identifier could not be bound”

If it's just one row, how about:

UPDATE LocalServer.DB.dbo.table 
SET [ChangeStatus] = 
(
  SELECT [ChangeStatus] 
  FROM RemoteServer.DB.dbo.table
) 
WHERE LocationID = '1';

Now you've eliminated the join and any aliases that have to be bound. Also why are you referencing both with 4-part names? If the local server is really the same instance, why not reference the local as DB.dbo.table instead of LocalServer.DB.dbo.table? If you take out the four-part name on the outer table, you can also do this for multi-row updates. It's not as pretty to do the aliasing this way, though:

UPDATE DB.dbo.table 
SET [ChangeStatus] = 
(
  SELECT [ChangeStatus] 
  FROM RemoteServer.DB.dbo.table
  WHERE key_col = DB.dbo.table.key_col
) 
WHERE LocationID = '1';

And as we discussed offline, if it's multiple rows and multiple columns, you should use something like this:

UPDATE t
SET [ChangeStatus] = r.[ChangeStatus],
    [OtherColumn]  = r.[OtherColumn]
FROM DB.dbo.table AS t
INNER JOIN RemoteServer.DB.dbo.table AS r
  ON t.key_col = r.key_col
WHERE t.LocationID = '1';

I believe that proper aliasing and eliminating the 4-part name should cause less work for the relevant parts of the execution process.

Best Answer

Related Solutions

Sql-server – Statement could not be prepared

Sql-server – Query intermittently fails with “The multi-part identifier could not be bound”

Related Question