如何检查存储过程是否成功返回表中的值

I have a stored procedure that runs a insert statement and it returns a value into a datatable. I must use a datatable thank you in advance :)

我有一个运行insert语句的存储过程，它将一个值返回到数据表中。我必须提前使用数据表谢谢你:)

my SQL stored procedure

我的SQL存储过程

begin
Declare @postID int = 0
insert into reviews (customerID, title, review)
values (@cusomerID, @title, @review) 
set @postID = SCOPE_IDENTITY()
end

the return value is stored in a datatable i would like to check if the insert was successful.

返回值存储在数据表中我想检查插入是否成功。

i'm using ajax jquery to do insert which calls the stored procedure and returns a value

我正在使用ajax jquery做insert调用存储过程并返回一个值

method called

方法叫

if (data.Rows[0]["post_ID "] != 0)
            {
                return "successful";
            }
else{
return "failed";
}

in javascript

在javascript中

if(result != "failed"){
run code
}

however it is returning successful even when the insert failed.

但即使插入失败，它也会返回成功。

any help would be great thank you.

任何帮助都会非常感谢你。

1 个解决方案

#1

The SCOPE_IDENTITY() returns the last auto-id. In your case it's returning the one before your failed insert.

SCOPE_IDENTITY（）返回最后一个auto-id。在你的情况下，它会在失败的插入之前返回一个。

Try something like

尝试类似的东西

BEGIN
    DECARE @postID int = 0
    INSERT INTO reviews (customerID, title, review) VALUES (@cusomerID, @title, @review) 
    IF @@ERROR <> 0
         SET @postID = -1
    ELSE
        SET @postID = (select SCOPE_IDENTITY())
   SELECT @postID
END

and in your code, if ID == -1 you know it failed.

在你的代码中，如果ID == -1，你知道它失败了。

Alternatively, you could

或者，你可以

BEGIN
    SET XACT_ABORT ON
    DECARE @postID int = 0
    INSERT INTO reviews (customerID, title, review) VALUES (@cusomerID, @title, @review) 
    SET @postID = (select SCOPE_IDENTITY())        
    SELECT @postID
END

and an exception will occur in your code upon any error (transaction is rolled back too).

并且在任何错误时您的代码中都会发生异常（事务也会被回滚）。

#1