Execution status on INSERT and UPDATE - MS SQL Server

Question

I am having a somewhat frustrating issue.

On the lower level: I want to be able to know whether an INSERT or UPDATE query in a proc was successful or not. I am not 100% sure if there is a status that is returned on all queries (apart from the SELECT). I know SQL Server gives a return type to all stored procs, and currently all mine have a return type of Integer.

On the middle level: In my repository, I want to use Entity Framework to call my stored procs and return the status as a converted (from int) boolean from the proc execution to my service.

On the higher level: I want to be able to use the returned boolean from my service to report back to an MVC controller on the task that was being performed.

On the most important layer of my issue, I have the following code:

public virtual ObjectResult<int> Proc_AddApprovalProcessor(string userId, string approverId, int approvalOrder)
{
     return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<int>(
        "EXECUTE [dbo].[Proc_AddApprovalProcessor] @userId, @approverId, @approvalOrder",
            new SqlParameter { ParameterName = "userId", Value = userId },
            new SqlParameter { ParameterName = "approverId", Value = approverId },
            new SqlParameter { ParameterName = "approvalOrder", Value = approvalOrder }).FirstOrDefault();
}

score 3 · Accepted Answer · answered Aug 24 '16 at 11:16

3

Make sure your insert/update stored procedures end with the following line:

SELECT @@ROWCOUNT

Then examine the return code and check that it is 1 (or however many rows you were expecting to insert/update).

answered Aug 24 '16 at 11:16

Perhaps it's worth mentioning that this might be misleading if there are triggers on the table. – Evaldas Buinauskas Aug 24 '16 at 11:31
@Evaldas Buinauskas Are you sure about this? Most of my update/insert/delete statements trigger entries in archive tables, and I have never had a problem with @@ROWCOUNT – Aug 24 '16 at 11:48
Perhaps it applies to more complex triggers, but perhaps you're right. This question seems to answer that http://stackoverflow.com/questions/7005225/sql-server-does-trigger-affects-rowcount sory for the confusion :) – Evaldas Buinauskas Aug 24 '16 at 11:50
I consider this to be the correct answer because triggers are normally not fired if the update or insert didn't succeed. I might be wrong though, please don't quote me. Anyway on the C# side, any number that is not 0 will always convert to a true and that is what I need. So even if @@ROWCOUNT is affectted by a trigger, it would only increase to a non-relevant number. I only require a 0 or a non-zero number to satisfy my logic on the repository/service/controller layers. Thanks for your comments guys – codeshinobi Aug 24 '16 at 12:17
@codeshinobi. Thank you. In the meantime, I have checked and can confirm that additional rows inserted or updated as a result of a trigger firing do not affect @@ROWCOUNT. Most of my production code would not work properly, if it were otherwise. One of those things that you research once and then forget about. It is only when someone questions it, that you have a sudden panic :-) . Glad to have helped and have a great day. – Aug 24 '16 at 14:06

Sampath · Answer 2 · 2016-08-24T12:41:20.097

3

You can use @@ROWCOUNT server variable immediately after the insert/update query to check number of affected rows by using the insert/update operation.

   declare @fName varchar(50) = 'my name',
            @lName varchar(50) = 'your name'
    INSERT INTO myTable(fName,lName) values(@fName,@lName)

    SELECT @@ROWCOUNT --> 0 - means no rows affected/nothing inserted or updated 
                     --> 1 - means row has been inserted or updated successfully

edited Aug 24 '16 at 12:41

answered Aug 24 '16 at 11:18

Sampath

63,341
64
307
441

Thanks for your time Sampath. The only issue is that print does not return anything to the executing proc and therefore would still yield the same results as not putting it there. I will upvote for @@ROWCOUNT. – codeshinobi Aug 24 '16 at 12:19
@codeshinobi Sure,No problem and thanks for the correctness of my mistake :) – Sampath Aug 24 '16 at 12:42

score 0 · Answer 3 · answered Aug 24 '16 at 11:43

0

if you are using transaction then have to use the below code to return the transaction count.

return @@TRANCOUNT;

answered Aug 24 '16 at 11:43

Suresh Shanmugam

1
2

I am not using transactions, but thanks for your comment. I will bookmark for later reference – codeshinobi Aug 24 '16 at 12:02

Execution status on INSERT and UPDATE - MS SQL Server

3 Answers3