SQL存储过程：删除基础资料前逻辑判断，检查是否有关联业务数据
SQL存储过程：删除基础资料前逻辑判断，检查是否有关联业务数据我们设计的业务系统，在删除基础资料（比如：客户资料、用户资料）前必须检查当前用户或客户是否关联业务数据、当前用户是否创建业务数据，若有关联业务数据是不可删除的，避免产生无效数据。
以下提供存储过程仅供参考。
删除客户资料存储过程：
SQL Code:
ALTER PROCEDURE [dbo].[usp_DeleteCustomer]
@CustomerID VARCHAR(30)--客户编码
AS
BEGIN
/************************************************************
功能：删除客户
SELECT * FROM dbo.tb_Customer
SELECT * FROM dbo.tb_OrderDetail
usp_DeleteCustomer 'admin'
usp_DeleteCustomer 'C-000003'
*************************************************************/
DECLARE @Count INT
SELECT @Count=0;
--检查业务关联
SELECT @Count=@Count+COUNT(1) FROM dbo.tb_OrderDetail WHERE CustomerId=@CustomerID
SELECT @Count=@Count+COUNT(1) FROM dbo.tb_GoodsDownDtl WHERE CustomerId=@CustomerID
SELECT @Count=@Count+COUNT(1) FROM dbo.tb_GoodsUpDtl WHERE CustomerId=@CustomerID
IF @Count>0
BEGIN
SELECT 0 AS Result,'当前客户关联业务数据，不可删除!' AS ErrMsg;
RETURN;
END ELSE
BEGIN
--删除客户资料
DELETE FROM tb_Customer WHERE CustomerID=@CustomerID
SELECT 1 AS Result,NULL AS ErrMsg;
END;
END
//来源:C/S框架网 | www.csframework.com | QQ:23404761
@CustomerID VARCHAR(30)--客户编码
AS
BEGIN
/************************************************************
功能：删除客户
SELECT * FROM dbo.tb_Customer
SELECT * FROM dbo.tb_OrderDetail
usp_DeleteCustomer 'admin'
usp_DeleteCustomer 'C-000003'
*************************************************************/
DECLARE @Count INT
SELECT @Count=0;
--检查业务关联
SELECT @Count=@Count+COUNT(1) FROM dbo.tb_OrderDetail WHERE CustomerId=@CustomerID
SELECT @Count=@Count+COUNT(1) FROM dbo.tb_GoodsDownDtl WHERE CustomerId=@CustomerID
SELECT @Count=@Count+COUNT(1) FROM dbo.tb_GoodsUpDtl WHERE CustomerId=@CustomerID
IF @Count>0
BEGIN
SELECT 0 AS Result,'当前客户关联业务数据，不可删除!' AS ErrMsg;
RETURN;
END ELSE
BEGIN
--删除客户资料
DELETE FROM tb_Customer WHERE CustomerID=@CustomerID
SELECT 1 AS Result,NULL AS ErrMsg;
END;
END
//来源:C/S框架网 | www.csframework.com | QQ:23404761
SQL Code:
ALTER PROCEDURE [dbo].[usp_DeleteEmployee]
@EmpID VARCHAR(30)--员工编码
AS
BEGIN
/************************************************************
功能：删除员工资料
SELECT * FROM dbo.tb_Employee
SELECT * FROM dbo.tb_GoodsDownDtl
usp_DeleteEmployee 'admin'
usp_DeleteEmployee 'E-000003'
usp_DeleteEmployee 'E-000012'
*************************************************************/
DECLARE @Count INT
SELECT @Count=0;
--检查业务关联
SELECT @Count=@Count+COUNT(1) FROM dbo.tb_GoodsDownDtl WHERE EmPId=@EmpID
SELECT @Count=@Count+COUNT(1) FROM dbo.tb_GoodsUpDtl WHERE EmPId=@EmpID
IF @Count>0
BEGIN
SELECT 0 AS Result,'当前员工关联业务数据，不可删除!' AS ErrMsg;
RETURN;
END ELSE
BEGIN
--删除客户资料
DELETE FROM tb_Employee WHERE EmpID=@EmpID
SELECT 1 AS Result,NULL AS ErrMsg;
END;
END
//来源:C/S框架网 | www.csframework.com | QQ:23404761
@EmpID VARCHAR(30)--员工编码
AS
BEGIN
/************************************************************
功能：删除员工资料
SELECT * FROM dbo.tb_Employee
SELECT * FROM dbo.tb_GoodsDownDtl
usp_DeleteEmployee 'admin'
usp_DeleteEmployee 'E-000003'
usp_DeleteEmployee 'E-000012'
*************************************************************/
DECLARE @Count INT
SELECT @Count=0;
--检查业务关联
SELECT @Count=@Count+COUNT(1) FROM dbo.tb_GoodsDownDtl WHERE EmPId=@EmpID
SELECT @Count=@Count+COUNT(1) FROM dbo.tb_GoodsUpDtl WHERE EmPId=@EmpID
IF @Count>0
BEGIN
SELECT 0 AS Result,'当前员工关联业务数据，不可删除!' AS ErrMsg;
RETURN;
END ELSE
BEGIN
--删除客户资料
DELETE FROM tb_Employee WHERE EmpID=@EmpID
SELECT 1 AS Result,NULL AS ErrMsg;
END;
END
//来源:C/S框架网 | www.csframework.com | QQ:23404761
扫一扫加微信
版权声明：本文为开发框架文库发布内容,转载请附上原文出处连接
NewDoc C/S框架网
