表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