ExecuteNonQuery returns -1

Question

I have a stored procedure and it returns a string value, and I have to execute this SP using ExecuteNonQuery statement. When I execute this, I get -1 as the return value.

I never used SET NOCOUNT ON. How can I get the return value? I did not declare the return value as OUTPUT -- just SELECT @VARIABLENAME. How can I get the value using ExecuteNonQuery?

IS not possibel for a procedure to return a string. It may produce a result set containing a column of type varchar or nvacrhar, or it may set an output parameter of type varchar or nvarchar. Return can only be int and you should never use return anyway. — Remus Rusanu
– Remus Rusanu, Commented Jul 16, 2012 at 10:15
I think you are confusing "return" with either an output parameter or a select statement. If you show us your procedure we can show you how to consume the value from C#. — Aaron Bertrand
– Aaron Bertrand, Commented Jul 16, 2012 at 12:14

vonPryz · Accepted Answer · 2012-07-16 08:27:15Z

8

ExecuteNonQuery is used to execute queries that return only the number of rows affected (though output parameters can be populated). -1 means that no rows were affected or you have set nocount on.

Use another an API call if you need to read data from the sproc.

answered Jul 16, 2012 at 8:27

vonPryz

24.3k10 gold badges58 silver badges69 bronze badges

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

1 Comment

Sivajith Over a year ago

Hi all, the problem i faced is get solved by setting an out put parameter and calling it as an out put parameter front end. And when i searched for solution, there is no way for doing it using 'ExecuteNonquery', since it returns the no.of rows affected. Thanks all for your replies and guiding...

Cericme · Accepted Answer · 2012-07-16 15:02:36Z

If you are only grabbing one value I would use the ExecuteScalar command if you are in a .Net Langauge. I know that Java and the like has an equivalent.

http://msdn.microsoft.com/en-us/ibrary/system.data.sqlclient.sqlcommand.executescalar.aspx

ExecuteNonQuery can get return data, if you have Output paramaters in your SP. This article explains how.

http://msdn.microsoft.com/en-us/library/80x06z3b(v=VS.71).aspx

Though again if you are just returning a string ExecuteScalar is simpler to use.

Collectives™ on Stack Overflow

ExecuteNonQuery returns -1

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related