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