SQL 存储过程无法更新数据，而其中的脚本手动运行

Question

我有一个在作业中执行的 SQL 脚本，用于定期转换表。为了加快速度，我将该脚本放入存储过程中并通过作业执行它。它工作得更快。

当存储过程即使执行但仍无法工作时，有时会出现此问题。我手动使用该脚本并运行它，一切正常。

整个脚本位于一个事务内，我已经这样做了，这样如果出现一些错误，它就会被正确记录。但是该脚本没有给出任何错误。

---

BEGIN TRANSACTION
BEGIN TRY

-- Rounding off Campaign stats
update Campaigns
set ImpressionsBurned = ImpressionTarget
where ImpressionsBurned > ImpressionTarget and CampaignTypeID = 1

update Campaigns
set ClicksConsumed = ClickTarget
where ClicksConsumed = ClickTarget and CampaignTypeID = 2

-- Updating Campaigns & Banners
update banners
set banners.impressionsburned = 
    (select  sum(impressionsqueue.impressionsburned) from impressionsqueue where impressionsqueue.bannerid = banners.bannerid)
where exists (select impressionsqueue.bannerid from impressionsqueue where impressionsqueue.bannerid = banners.bannerid)

update Campaigns
set ImpressionsBurned = 
    (select isnull(SUM(ImpressionsQueue.ImpressionsBurned),0) from ImpressionsQueue 
    inner join Banners on Banners.BannerID = ImpressionsQueue.BannerID and CampaignID = Campaigns.CampaignID
    )
where 
    exists (
        (select ImpressionsQueue.BannerID from ImpressionsQueue 
        inner join Banners on Banners.BannerID = ImpressionsQueue.BannerID and CampaignID = Campaigns.CampaignID
        )
    )
    and 
    (
    ( Campaigns.ImpressionsBurned < Campaigns.ImpressionTarget and Campaigns.CampaignTypeID = 1)
    or ( Campaigns.ClicksConsumed < Campaigns.ClickTarget and Campaigns.CampaignTypeID = 2) 
    or Campaigns.CampaignTypeID = 3
    )
    and Campaigns.IsPaused = 0
    and Campaigns.StartsOn <= GetDate() and Campaigns.EndsOn >= GetDate()

-- Updating Paused Banners in the Queue
update ImpressionsQueue
set IsPaused = (
    select IsPaused from Banners where Banners.BannerID = ImpressionsQueue.BannerID
)
-- Updating the Navigation URLs
update ImpressionsQueue
set NavigateURL = (
    select NavigateURL from Banners where Banners.BannerID = ImpressionsQueue.BannerID
)

-- Removing any rows if the Weight of a banner is updated, 
-- the banner will be reinserted later with updated impressions
delete from ImpressionsQueue 
where BannerID in 
( 
    select ImpressionsQueue.BannerID from ImpressionsQueue 
    inner join Banners on Banners.BannerID = ImpressionsQueue.BannerID
    group by ImpressionsQueue.BannerID, Banners.Weight
    having Banners.Weight <> COUNT(ImpressionsQueue.BannerID)
)

-- Removing entries whose impressions count has reached its target 
delete from impressionsqueue where
BannerID in (
    select Banners.BannerID from impressionsqueue
    inner join Banners on banners.bannerid = impressionsqueue.bannerid
    inner join campaigns on campaigns.campaignid = banners.campaignid
    where Campaigns.CampaignTypeID = 1 
    -- excluding flat fee based campaign
    group by Campaigns.ImpressionTarget, Banners.BannerID
    having not (Campaigns.ImpressionTarget > sum(ImpressionsQueue.impressionsburned))
    -- inverse logic for campaign count
)
-- Removing entries whose campaign click count has reached
delete from impressionsqueue where
BannerID in (
    select Banners.BannerID from impressionsqueue
    inner join Banners on banners.bannerid = impressionsqueue.bannerid
    inner join campaigns on campaigns.campaignid = banners.campaignid and Campaigns.CampaignTypeID = 2 
        and Not (Campaigns.ClickTarget > Campaigns.ClicksConsumed)
    -- inverse logic for campaign count
)
-- Removing entries whose campaign has expired
delete from impressionsqueue where
BannerID in (
    select impressionqueueid from impressionsqueue
    inner join Banners on banners.bannerid = impressionsqueue.bannerid
    inner join campaigns on campaigns.campaignid = banners.campaignid
        and not (Campaigns.StartsOn <= GETDATE() and Campaigns.EndsOn >= GETDATE())
    -- inverse logic for date
)

----------------------------------------------------------------------------------------
-- Begin updating Impressions Queue
----------------------------------------------------------------------------------------


Declare @TempBanners Table(BcID [int] IDENTITY(1,1),ZoneID [int], BannerID [int], Weight [int], FileID [uniqueidentifier], IsPaused [bit], Impressions [int], URL [nvarchar](2048))

/*
Insert into the temp table all the banners that:
      1) Belong to an active campaign => c.StartsOn <= GetDate() and c.EndsOn >= GetDate()
      2) and Belong to a campaign that is not paused
      3) and The banner itself is not paused
      4) and Flat Fee campaign banner
*/
Insert Into @TempBanners (ZoneID, BannerID, Weight, FileID, IsPaused, Impressions, URL)
Select 
      bz.ZoneID, 
      b.BannerID, 
      b.Weight,
      b.FileID,
      b.IsPaused,
      b.ImpressionsBurned,
      b.NavigateURL
From Banners b
Join Bannerzones bz on b.BannerID = bz.BannerID
Join Campaigns c on b.CampaignID = c.CampaignID
And c.StartsOn <= GetDate() and c.EndsOn >= GetDate()
And c.IsPaused = 0
And b.IsPaused = 0
And (
            (c.CampaignTypeID = 1 And c.ImpressionsBurned < c.ImpressionTarget)
            Or (c.CampaignTypeID = 2 And c.clicksconsumed < c.clicktarget)
            Or (c.campaigntypeid = 3)
      );

-- Declaration of Vairables      
Declare @index As int,@maxRow As int, @weight Int, @bcid int
Set @index = 1 --Because we will start from First row
Select @maxRow = Count(0)  From @TempBanners Where Weight > 1  
-- How many rows we have that has weight more then 1
While(@index <= @maxRow)
Begin
      Select 
            @weight = V.Weight,
            @bcid = V.BcID
      From (
                  Select 
                        BCid,
                        Weight,
                        ROW_NUMBER() Over (Order By Bcid Asc) Row 
                  From @TempBanners 
                  Where Weight > 1 
            )V
      Where V.Row = @index

      Set @index = @index + 1 

      While(@weight <> 1)
      Begin
            Insert Into @TempBanners (ZoneID, BannerID, Weight, FileID, URL)
            Select ZoneID, BannerID, Weight, FileID, URL From @TempBanners Where BcID = @bcid 
            set @weight = @weight - 1
      End
End

-- INSERT INTO THE IMPRESSION QUEUE ALL THE BANNERS THAT ARE NOT YET IN THE QUEUE
Insert Into ImpressionsQueue (BannerID, ZoneID, FileID, ImpressionsBurned,  RowUpdated, IsPaused, NavigateURL )
Select V.BannerID, V.ZoneID, V.FileID, V.Impressions, GETDATE(), V.IsPaused, V.URL
From 
(
      Select m.BannerID, m.ZoneID, m.FileID, 
      isnull(m.IsPaused,0) as IsPaused, 
      isnull(m.Impressions,0) as Impressions,
      ISNULL(m.URL,'') as URL
      From @TempBanners m
      where Weight > 
            (select COUNT(t.bannerid) 
            from ImpressionsQueue as t
            where t.BannerID = m.BannerID
                and t.FileID = m.FileID
                and t.ZoneID = m.ZoneID
                )      
)As V;

-- Update the banner file id in case it is modified after the banner was created
Update ImpressionsQueue
Set FileID = CTE.FileID, ImpressionsQueue.IsPaused = isnull(CTE.IsPaused,0), ImpressionsQueue.NavigateURL = CTE.URL
From ImpressionsQueue I
Inner Join @TempBanners As CTE On CTE.BannerID = I.BannerID And CTE.ZoneID = I.ZoneID --And CTE.FileID <> I.FileID

----------------------------------------------------------------------------------------
-- End updating Impressions Queue
----------------------------------------------------------------------------------------


END TRY
BEGIN CATCH
    declare @error varchar(3000) = 'Message: ' + ERROR_MESSAGE() + ' ' + ERROR_LINE()
    exec master.sys.xp_logevent 50001, @error , 'Warning' 
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
END CATCH
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION

Answer 1

不确定这是否是所有问题的原因 - 但如果我是你，我会将 COMMIT TRANSACTION 放入 TRY..... 块中：

   -------------------------------------------------------------------------------
   -- End updating Impressions Queue
   -------------------------------------------------------------------------------
   IF @@TRANCOUNT > 0
       COMMIT TRANSACTION
END TRY
BEGIN CATCH
    declare @error varchar(3000) = 'Message: ' + ERROR_MESSAGE() + ' ' + ERROR_LINE()
    exec master.sys.xp_logevent 50001, @error , 'Warning' 
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
END CATCH

如果一切顺利，你将执行 < code>COMMIT TRANSACTION 作为 TRY... 块的最后一条语句 - 如果出现问题，无论如何您都会陷入 CATCH 块。在 CATCH 块之外调用 COMMIT TRANSACTION 可能很危险......