SQL更新不会引发错误

I have this simple SQL Update

我有这个简单的SQL更新

IF(@MyID IS NOT NULL)
 BEGIN
  BEGIN TRY
   UPDATE DATATABLE
    SET Param1=@Param1, Data2=@Data2,...
    WHERE MyID=@MyID
   END TRY
   BEGIN CATCH
    SELECT ERROR_MESSAGE() AS 'Message' 
    RETURN -1
   END CATCH

   SELECT * FROM DATATABLE WHERE MyID= @@IDENTITY
   SET @ResultMessage = 'Succefully Inserted' 
   SELECT @ResultMessage AS 'Message' 
   RETURN 0
 END

The problem is that when I provide an invalid ID, one that does not exist it, does not throw an error I still get an error code of 0 with the Successfully inserted message. I also added this after the catch. Still nothing, am I missing something fundamental?

问题是,当我提供一个无效的ID,一个不存在的ID时,不会抛出错误,我仍然会在成功插入消息的情况下得到错误代码0。我还在捕获后添加了这个。仍然没有,我错过了一些基本的东西吗?

END CATCH
IF(@@ERROR != 0)
BEGIN
    SET @ResultMessage = 'Not Successful Inserted' 
    SELECT @ResultMessage AS 'Message' 
    RETURN -1
END
SELECT * FROM DATATABLE WHERE MyID= @@IDENTITY
SET @ResultMessage = 'Succefully Inserted' 
SELECT @ResultMessage AS 'Message' 
RETURN 0

Is there something special I am suppose to look for?

我想要寻找一些特别的东西吗?

3 个解决方案

#1

SQL will catch errors, but an UPDATE statement that does not update any rows, is a valid SQL statement and should not return an error. You can check @@RowCount to see how many rows the update statement actually updated

SQL将捕获错误,但不更新任何行的UPDATE语句是有效的SQL语句,不应返回错误。您可以检查@@ RowCount以查看update语句实际更新的行数

  IF(@MyID IS NOT NULL)
     BEGIN
      BEGIN TRY
       UPDATE DATATABLE
        SET Param1=@Param1, Data2=@Data2,...
        WHERE MyID=@MyID
        IF @@RowCount = 0
        BEGIN
           SELECT 'No record found...' AS Message
           RETURN -1
        END
       END TRY
       BEGIN CATCH
        SELECT ERROR_MESSAGE() AS 'Message' 
        RETURN -1
       END CATCH

       SELECT * FROM DATATABLE WHERE MyID= @@IDENTITY
       SET @ResultMessage = 'Succefully Inserted' 
       SELECT @ResultMessage AS 'Message' 
       RETURN 0
     END

#2

You can replace

你可以替换

IF(@MyID IS NOT NULL)

with

IF(@MyID IS NOT NULL) OR NOT EXISTS (
    SELECT 1
    FROM DATATABLE
    WHERE MyID=@MyID
)

Or you may take Sparky's solution. Depends on what logic is more suitable for you. Should an error message be thrown or not if @MyID is not valid?

或者您可以采用Sparky的解决方案。取决于哪种逻辑更适合您。如果@MyID无效,是否应该抛出错误消息?

#3

I would so something like this .... WRAP the update statement in a Transaction and rollback on failure

我会这样的......在事务中WRAP更新语句并在失败时回滚

IF(@MyID IS NOT NULL)
BEGIN
   BEGIN TRY

       BEGIN TRANSACTION
           UPDATE DATATABLE
            SET Param1=@Param1, Data2=@Data2,...
            WHERE MyID=@MyID
            SET @ResultMessage = 'Succefully Inserted' 
            SELECT @ResultMessage AS 'Message' 
            RETURN 0
        COMMIT TRANSACTION 
   END TRY

   BEGIN CATCH
    IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION  

        SELECT ERROR_MESSAGE() AS 'ErrorMessage' 
        SET @ResultMessage = 'Not Successful Inserted' 
        SELECT @ResultMessage AS 'Message' 
        RETURN -1
    END CATCH
END

#1