ExecuteNonQuery returns -1
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?
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.
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.
ExecuteNonQuery can get return data, if you have Output paramaters in your SP. This article explains how.
Though again if you are just returning a string ExecuteScalar is simpler to use.