存储过程的某些部分没有运行

Question

我有一个存储过程，它运行一系列插入，但在某些情况下，似乎其中一个插入没有运行，但后面的代码运行良好。

DECLARE @ID1 int
DECLARE @ID2 int
-- This works
INSERT INTO table1 (field1,field2)
VALUES('test', 1)
SELECT @ID1 = SCOPE_IDENTITY() -- This picks up the correct ID
-- This sometimes doesn't seem to run or an error of some sort happens infrequently
INSERT INTO table2 (field1, field2, field3, field4, field5)
VALUES(1,2,3,4,@ID1)
-- This always runs, but if the previous insert doesn't run inserts the previous ID into @ID2
SELECT @ID2 = SCOPE_IDENTITY()
-- Inserts the ID from the previous step and therefore is sometimes wrong
INSERT INTO table3 (field1,field2)
VALUES ('testing', @ID2)

这是SP的编辑样本。为什么有时会发生这种情况?我本以为如果INSERT有问题剩下的程序就会爆炸?但是同样的代码在大多数情况下都是由同一个人运行的。我已经检查了是否有任何表锁定正在进行在问题的同时，但似乎没有什么我可以发现。

找出问题所在的最好方法是什么?或者我应该如何重构上面的代码来消除可能的影响?

Answer 1

将TRY / CATCH放在second INSERT周围查看可能的错误

Answer 2

检查插入行后的@@Error，看看是否抛出了一个"silent"错误。您还可以尝试在try/CATCH块中包装插入行，但我怀疑这不会富有成效，因为我假设您在管理工作室的输出窗口中没有得到任何错误?

尝试在运行存储过程的同时运行SQL Profiler，看看它是否提供有关失败的任何进一步信息。

对存储过程的以下更改可能会从SP本身中提供尽可能多的关于正在发生的事情的信息。这不是完美的代码，但它应该提供一个体面的指示，任何错误发生和被"吞下":

DECLARE @ID1 int
DECLARE @ID2 int
-- This works
INSERT INTO table1 (field1,field2)
VALUES('test', 1)
SELECT @ID1 = SCOPE_IDENTITY() -- This picks up the correct ID
-- This sometimes doesn't seem to run
IF @@error <> 0
BEGIN
    PRINT 'Error before: ' + (cast @@error as varchar)
END
BEGIN TRY
    INSERT INTO table2 (field1, field2, field3, field4, field5)
    VALUES(1,2,3,4,@ID1)
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
IF @@error <> 0
BEGIN
    PRINT 'Error after: ' + cast(@@error as varchar)
END
-- This always runs, but if the previous insert doesn't run inserts the previous ID into @ID2
SELECT @ID2 = SCOPE_IDENTITY()
-- Inserts the ID from the previous step and therefore is sometimes wrong
INSERT INTO table3 (field1,field2)
VALUES ('testing', @ID2)

Answer 3

使用try/catch和in catch语句引发错误。如-:

CREATE PROC Usp_InsertBulkRecord
AS
BEGIN
BEGIN TRY
    DECLARE @ID1 int
    DECLARE @ID2 int
-- This works
INSERT INTO table1 (field1,field2)
VALUES('test', 1)
SELECT @ID1 = SCOPE_IDENTITY() -- This picks up the correct ID
-- This sometimes doesn't seem to run
INSERT INTO table2 (field1, field2, field3, field4, field5)
VALUES(1,2,3,4,@ID1)
-- This always runs, but if the previous insert doesn't run inserts the previous ID into @ID2
SELECT @ID2 = SCOPE_IDENTITY()
-- Inserts the ID from the previous step and therefore is sometimes wrong
INSERT INTO table3 (field1,field2)
VALUES ('testing', @ID2)
END TRY              
  BEGIN CATCH              
  DECLARE @Error varchar(8000)                                                            
    SET @Error= Convert(varchar,ERROR_NUMBER()) + '*****' + Convert(varchar(4000),ERROR_MESSAGE())                                                             
    + '*****' + isnull(Convert(varchar,ERROR_PROCEDURE()),'[Usp_InsertBulkRecord]')                     
    + '*****' + Convert(varchar,ERROR_LINE()) + '*****' + Convert(varchar,ERROR_SEVERITY())                         
    + '*****' + Convert(varchar,ERROR_STATE())                                                            
    RAISERROR (@Error,16,1);               
  END CATCH 
END