1

UPDATE: my code is behaving as expected by there was a typo in the stored procedure that was the reason it was failing.

I can't seem to figure out why or how to fix this because I am not getting any errors what I am getting is the return value is 0 which means fail.

Here is my .net code:

SqlParameter returnValue= new SqlParameter("returnValue", SqlDbType.Int); returnValue.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(returnValue); cmd.ExecuteNonQuery(); result = Convert.ToInt32(returnValue.Value); //1 success and 0 failed

My stored procedure:

CREATE PROCEDURE EmployeeUpdate @employee_id BIGINT, @name nvarchar(250) AS BEGIN SET NOCOUNT ON DECLARE @Result int SET @Result = 0 UPDATE Employee SET name = @name WHERE employee_id = @employee_id IF (@@rowcount = 1) BEGIN SET @Result = 1 END SET NOCOUNT OFF RETURN @Result END

So if I just execute the stored procedure from SQL Server Management Studio, it does update my row successfully without any error

EXEC EmployeeUpdate 34,'John John' Return Value = 1
Improve this question

2 Answers 2

Reset to default
2

Replace the following

CREATE PROCEDURE EmployeeUpdate @employee_id BIGINT, @name nvarchar(250) AS BEGIN SET NOCOUNT ON

With Following

CREATE PROCEDURE EmployeeUpdate @employee_id BIGINT, @name nvarchar(250) AS BEGIN SET NOCOUNT OFF

SET NOCOUNT ON is indicating that number of rows effect by T-SQL will not be returned

SET NOCOUNT OFF mean that number of rows effect by T-SQL will be returned.

enter image description here enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

is there any other way to debug?
Yes, You have an another way. Please go to the database. Now, execute the Stored-Proc in the SSMS. Like this, EmployeeUpdate 1, 'PankajGarg'. Here it can be checked what is the actual result being returned using Set NOCount ON/OFF
1

I would suggest to remove returnValue parameter altogether and just use the return value of ExecuteNonQuery() method instead:

int rowsAffected = cmd.ExecuteNonQuery(); result = rowsAffected == 1 ? 1 : 0;

Stored procedure:

CREATE PROCEDURE EmployeeUpdate @employee_id BIGINT, @name nvarchar(250) AS BEGIN UPDATE Employee SET name = @name WHERE employee_id = @employee_id END
Improve this answer

6 Comments

Do you have any triggers on that table that might cause a rollback? What do you get in SQL Management Studio if you run the simplified version of the stored procedure above? i.e. EXEC EmployeeUpdate 34,'John John'
dont have any triggers on table just double check that.
i get the return value = 1 and when i checked the table and i see my updated values
You need to run "simplified" sp from my answer - i.e. without SET NOCOUNT ON line. That's what causing ExecuteNonQuery() to return -1 in your case.
but i am doing set nocount off before it returns i will give it a shot and see
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.