sql连接删除

最新推荐文章于 2023-03-16 16:50:28 发布

zhaoyabin

最新推荐文章于 2023-03-16 16:50:28 发布

阅读量728

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL 文章标签： sql delete join output date 脚本

本文链接：https://blog.csdn.net/zhaoyabin/article/details/5715255

SQL 专栏收录该内容

4 篇文章

订阅专栏

此存储过程用于从数据库中删除特定产品的指定期次的所有中奖记录及相关联的订单详情。涉及多个表的级联删除操作，并确保事务的一致性和回滚机制。

USE [LotteryData]
GO
/****** 对象: StoredProcedure [dbo].[sp_DeleteAward]    脚本日期: 07/06/2010 10:09:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  yabin.zhao
-- Create date: 6.22
-- Description: 删除中奖
-- =============================================
ALTER PROCEDURE [dbo].[sp_DeleteAward]
@Status int output,
@issueid varchar(5),
    @productid int,
    @EngineID int
AS
BEGIN
    begin transaction
     /*声明变量*/
    declare @ErrorMsg varchar(200)
set @Status = 0
     /*初始化变量*/
    delete from award from AwardGradeDetail award inner join Lotteryorder lo on lo.OrderID=award.AwardOrderID where lo.ProductID=@productid and lo.IssueID=@issueid and lo.EngineID=@EngineID
    if @@error<>0
         begin
           set @ErrorMsg='删除中奖出错'
           set @Status=1
           goto EXIT_ERROR
         end
    delete from aod from AwardOrderDetail aod inner join Lotteryorder lo on lo.OrderID=aod.AwardOrderID where lo.ProductID=@productid and lo.IssueID=@issueid and lo.EngineID=@EngineID
    if @@error<>0
         begin
           set @ErrorMsg='删除详细出错'
           set @Status=1
           goto EXIT_ERROR
         end
    delete from ao from AwardOrder ao inner join Lotteryorder lo on lo.OrderID=ao.AwardOrderID where lo.ProductID=@productid and lo.IssueID=@issueid and lo.EngineID=@EngineID
    if @@error<>0
         begin
           set @ErrorMsg='删除订单出错'
           set @Status=1
           goto EXIT_ERROR
         end
    delete from AwardIssue where IssueID=@issueid and LotteryID = @productid
    if @@error<>0
         begin
           set @ErrorMsg='删除期次出错'
           set @Status=1
           goto EXIT_ERROR
         end
   commit transaction
   goto EXIT_END

     EXIT_ERROR:
           rollback transaction
           raiserror(@ErrorMsg,16,1)
EXIT_END:
END
================================================================

两个表连接删除