CODEWITHSUNDEEP
c#sqlsql-server
Jesus Torres
Jesus Torres

Reputation: 63

Return value on stored procedure

I have a problem, when I execute this stored procedure:

IF NOT EXISTS (SELECT * FROM TBL_M 
               WHERE TYPE_M = 'reservation' 
                 AND NUM_DESK = @NUMBER_DESK 
                 AND ID_TIME = @ID_TIME)
BEGIN
    INSERT INTO TBL_M 
    VALUES ('reservation', @NUMBER_DESK, @NUM_USER, @ID_TIME)
END

Everything works correctly, the only problem is that I want to know when the insert is executed the insert and when not.

In C# I use the following method ExecuteNonQuery()

But it always returns -1, how can you identify the insert and when it doesn't insert.

Upvotes: 1

Views: 89

Answers (1)

Srinika Pinnaduwage
Srinika Pinnaduwage

Reputation: 1042

Declare a variable 

Declare @ROWCOUNT INT = 0

Assign the variable

Select @ROWCOUNT = @@ROWCOUNT

Just after the insert (inside begin-end) either return or select (then you have to to use ExecuteQuery instead of ExecuteNonQuery), or have an output parameter, to get the value of @ROWCOUNT.

@MickyD ' s suggestion:

The SQL variable @@ROWCOUNT hold the number of rows affected by the last statement, in this case just after executing Insert. If any other operation is performed, that value will be not the same any more, hence assign that to a locally defined variable and pass back to the caller.

Upvotes: 1

Related Questions