具有多个连接的 UPDATE Select 语句

Question

我正在尝试根据 Project_CSTM 表中的数据更新 Contacts_CSTM 表中的数据。这是我正在使用的查询，但出现错误：“从字符串转换为 uniqueidentifier 时转换失败”

 ALTER PROCEDURE Insurance_Check_Expiration
  @ID_C AS NVARCHAR (55) = ID_C
  AS
 BEGIN      
 SET NOCOUNT ON
 IF EXISTS(SELECT * FROM CONTACTS_CSTM WHERE ID_C = @ID_c)

 Update contacts_cstm set insurance_expired_label_c = 'INSURANCE EXPIRED'
 WHERE DRIVERS_LICENSE_NUMBER_C IS NOT NULL AND @ID_C=
 (SELECT cc.id_c
 FROM PROJECT_CSTM PC 
 JOIN PROJECT P 
 ON P.ID = PC.ID_C
 JOIN PROJECT_RELATION PR
 ON PR.PROJECT_ID = P.ID  
 JOIN CONTACTS C
 ON C.ID = PR.RELATION_ID 
 JOIN CONTACTS_CSTM CC
 ON CC.ID_C = C.ID
 WHERE CC.ID_C = @ID_C AND INSURANCE_EXPIRED_C ='1') 

谢谢。

Answer 1

其一，您将 @ID_C 的值设置为一个值 (ID_C)，这显然不是有效的 GUID。

这与您所做的功能等效，运行它，您会得到相同的错误。

CREATE PROCEDURE Insurance_Check_Expiration
  @ID_C AS NVARCHAR (55) = ID_C
  AS
 BEGIN      
 DECLARE @A UNIQUEIDENTIFIER

 SET @A = @ID_C


 END

 exec Insurance_Check_Expiration

编辑：这是一个基于OP评论的功能示例：

CREATE TABLE GUIDExample (ID_C UNIQUEIDENTIFIER)
GO
CREATE PROC GuidExample_Insert @ID_C UNIQUEIDENTIFIER
AS
BEGIN
    SELECT @ID_C
END
GO
CREATE TRIGGER GUID_Example ON GUIDExample
AFTER INSERT
AS
    DECLARE @ID_C UNIQUEIDENTIFIER

    SELECT @ID_C = ID_C
    FROM Inserted


    EXEC GuidExample_Insert @ID_C
GO

DECLARE @SampleGUID UNIQUEIDENTIFIER

SET @SampleGUID = NEWID()

INSERT GUIDExample (ID_C)
VALUES(@SampleGUID)