存储过程 删除两表关联的数据记录

表Device为设备表（主表），如下图

表Rxparam为参数表(从表)，如下图

该存储过程是查找Device表中CustomerID为62的所有设备Rxparam表对应的记录都删除。

CREATE PROCEDURE CopyCustomerInfo
(
  @FromCustomerID int,
  @ToCustomerID int,
  @CustomerType int
)
AS
BEGIN TRANSACTION
     DECLARE @Error int
     SET @Error=0
     DECLARE @DeviceCount int
     SET @DeviceCount=0

BEGIN

        SELECT @DeviceCount=COUNT(*) FROM Device WHERE CustomerID=@FromCustomerID;
         IF @DeviceCount>0
            BEGIN
              DELETE FROM RXParam WHERE DeviceID IN (SELECT DeviceID FROM Device WHERE CustomerID=@FromCustomerID);  --删除从表对应记录
              DELETE FROM Device WHERE CustomerID=@FromCustomerID;  --删除主表记录
              SET @Error=@Error+@@ERROR   --记录错误
            END
        IF @Error>0
           rollback  transaction;       --执行回滚
        ELSE
           COMMIT transaction;        --提交事务
END
END

 

exec CopyCustomerInfo