Resolve the error: The multi-part identifier could not be bound?

94 views Asked by At

I am creating a stored procedure and the final "When not matched" statement is throwing an error for the tmp.DelDate and tmp.DelUser fields. The "tmp" table is a User-Defined Table Type and the definition is below the sp code. 99 times out of 100, the problem is a bad alias or other typo. I've been staring at this and I have to be missing something small. This last statement is almost identical to the first "When Matched" statement.

ALTER Procedure dbo.spInsertUpdateProtocolRiskStrats    
    @riskStratsTable ProtocolRiskStrats READONLY

WITH RECOMPILE

AS 
BEGIN

WITH riskStrats as (
    SELECT ol.StratId,
        ol.LinkType,
        ol.LinkId,
        ol.add_user,
        ol.add_date,
        ol.del_user,
        ol.del_date
    FROM ots_StratTriggerOutcomesLinks ol
    JOIN @riskStratsTable rst on ol.LinkId = rst.LinkId 
    WHERE ol.LinkId = rst.LinkId
    AND ol.LinkType = 2
)

MERGE riskStrats
USING @riskStratsTable as tmp
ON riskStrats.LinkId = tmp.LinkId

WHEN MATCHED THEN 
    UPDATE SET riskStrats.add_date = tmp.AddDate, 
            riskStrats.add_user = tmp.AddUser,
            del_date = null,
            del_user= null

WHEN NOT MATCHED THEN
    INSERT (StratId, LinkType, LinkId, add_user, add_date)
    VALUES (tmp.StratId, tmp.LinkType, tmp.LinkId, tmp.AddUser, tmp.AddDate)

WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET riskStrats.del_date = tmp.DelDate,
            riskStrats.del_user = tmp.DelUser;

END

User Table definition

CREATE TYPE dbo.ProtocolRiskStrats AS TABLE 
(
    KeyId int null,
    StratId int null,
    LinkType int null,
    LinkId int null,
    AddUser int null,
    AddDate datetime null,
    DelUser int null,
    DelDate datetime null
)

1

There are 1 answers

0
USMC6072 On

As noted by @AlwaysLearning, I was assigning values that couldn't exist because it was a "not matched" condition. I updated my last statement to use constant values. I had to add another parameter to pass in user name. I could have also done a "Top 1" on my TVP but my dev lead didn't like that.

    UPDATE SET riskStrats.del_date = GETDATE(),
            riskStrats.del_user = @userName;