thefumbler
asked on
stored procedure always returns nonzero
I'm missing something really simple here...in testing a sql 2005 sp in asp.net 2.0 I whittled it down to this and "myresult" always returns -1, whereas I would expect 0 as success. WHY?
objConn.Open()
Dim myresult As Integer = 0
Dim strSQL As String = "uspTEST"
Dim objCmd As New SqlCommand(strSQL, objConn)
objCmd.CommandType = CommandType.StoredProcedur
myresult = objCmd.ExecuteNonQuery() *******************
===
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[uspTEST]
AS
DECLARE @saveerror int
SET @saveerror=0
select 'test'
set @saveerror=@@ERROR
return @saveerror
objConn.Open()
Dim myresult As Integer = 0
Dim strSQL As String = "uspTEST"
Dim objCmd As New SqlCommand(strSQL, objConn)
objCmd.CommandType = CommandType.StoredProcedur
myresult = objCmd.ExecuteNonQuery() *******************
===
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[uspTEST]
AS
DECLARE @saveerror int
SET @saveerror=0
select 'test'
set @saveerror=@@ERROR
return @saveerror
Since the ExecuteNonQuery method returns the Records Affected when there is a a SELECT, INSERT, UPDATE, DELETE query, in your case none of those are present, hence it is not supported and therefore -1.
Let's try that again (correction):
Since the ExecuteNonQuery method returns the Records Affected when there is a a INSERT, UPDATE, DELETE query, in your case none of those are present, hence it is not supported and therefore -1.
Here is what you would read in the Help for the ExecuteNonQuery method:
<quote>
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.
</quote>
Since the ExecuteNonQuery method returns the Records Affected when there is a a INSERT, UPDATE, DELETE query, in your case none of those are present, hence it is not supported and therefore -1.
Here is what you would read in the Help for the ExecuteNonQuery method:
<quote>
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.
</quote>
all you need to do is, add parameter to your command which is ReturnValue parameter "System.Data.ParameterDire
>> use ExecuteScalar, your return value will be stored in the parameter you have added.<<
No, that is not the way the ExecuteScalar method works. This is from the Help:
<quote>
Return Value
The first column of the first row in the result set, or a null reference if the result set is empty.
</quote
No, that is not the way the ExecuteScalar method works. This is from the Help:
<quote>
Return Value
The first column of the first row in the result set, or a null reference if the result set is empty.
</quote
>>No, that is not the way the ExecuteScalar method works. <<
have you tried my solution practically? if not, than do it first and let me know whether you are getting value back in your "ReturnParameter" or not.
have you tried my solution practically? if not, than do it first and let me know whether you are getting value back in your "ReturnParameter" or not.
Why don't we focus on the question posed by the author? Here it is again:
"myresult" always returns -1, whereas I would expect 0 as success. WHY?"
myresult is the return value for the .NET ExecuteNonQuery method. But as I pointed out in my first comment the ExecuteNonQuery method returns the records affected when doing an INSERT, UPDATE or DELETE. Since the author's Stored Procedure, does not appear to contain any of those 3 then the result will always be -1.
But don't take my word for it, here is the documentation on the ExecuteNonQuery Method:
SqlCommand..::.ExecuteNonQ
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx
<quote>
You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.
Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data.
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.
</quote>
"myresult" always returns -1, whereas I would expect 0 as success. WHY?"
myresult is the return value for the .NET ExecuteNonQuery method. But as I pointed out in my first comment the ExecuteNonQuery method returns the records affected when doing an INSERT, UPDATE or DELETE. Since the author's Stored Procedure, does not appear to contain any of those 3 then the result will always be -1.
But don't take my word for it, here is the documentation on the ExecuteNonQuery Method:
SqlCommand..::.ExecuteNonQ
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx
<quote>
You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.
Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data.
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.
</quote>
>>Why don't we focus on the question posed by the author? Here it is again:<<
author has used "Return" statement and to handle value which is "Return" in stored procedure, he should have "return parameter" from .NET code. he don't want to see what records are affected, only want to handle "return" value from SP which is NOT AT ALL possible with ExecuteNonQuery!!.
author has used "Return" statement and to handle value which is "Return" in stored procedure, he should have "return parameter" from .NET code. he don't want to see what records are affected, only want to handle "return" value from SP which is NOT AT ALL possible with ExecuteNonQuery!!.
>>he don't want to see what records are affected, only want to handle "return" value<<
Of course they do not want the records affected. I never said they did. They know perfectly well there are no records affected.
>>which is NOT AT ALL possible with ExecuteNonQuery!!.<<
Huh? Of course, it is possible to get the return parameter using the .NET ExecuteNonQuery method. You really need to check your facts first.
But again, this is not about you. I suggest we wait until the author gives us some feedback.
Of course they do not want the records affected. I never said they did. They know perfectly well there are no records affected.
>>which is NOT AT ALL possible with ExecuteNonQuery!!.<<
Huh? Of course, it is possible to get the return parameter using the .NET ExecuteNonQuery method. You really need to check your facts first.
But again, this is not about you. I suggest we wait until the author gives us some feedback.
ASKER
In my test example, changing the code from ExecuteNonQuery to ExecuteScalar and adding the lines
objCmd.Parameters.Add("@Re
objCmd.Parameters("@Return
results in an InvalidCastException : 'Conversion from string "test" to type 'Integer' is not valid.'
In my real code of course its not a simple select statement, there are an assortment of updates and deletes and a rollback of the entire procedure if any of them fail. Its now clear as to WHY that occurs because its an unsupported SELECT statement in the stored procedure executed as a result of ExecuteNonQuery .... but can't I somehow to determine a success or failure of any stored procedure (regardless of whatever is inside such as SELECT, INSERT, DELETE or UPDATES in it) so that I can code accordingly. Simply, I want to knowis my stored procedure is successful, True or False?
objCmd.Parameters.Add("@Re
objCmd.Parameters("@Return
results in an InvalidCastException : 'Conversion from string "test" to type 'Integer' is not valid.'
In my real code of course its not a simple select statement, there are an assortment of updates and deletes and a rollback of the entire procedure if any of them fail. Its now clear as to WHY that occurs because its an unsupported SELECT statement in the stored procedure executed as a result of ExecuteNonQuery .... but can't I somehow to determine a success or failure of any stored procedure (regardless of whatever is inside such as SELECT, INSERT, DELETE or UPDATES in it) so that I can code accordingly. Simply, I want to knowis my stored procedure is successful, True or False?
In order to return OUTPUT parameters (a RETURN parameter is a special type of output parameter) you MUST use the ExecuteNonQuery method. Period.
So all you have to do is declare the RETURN parameter as you have done and use the ExecuteNonQuery method.
So all you have to do is declare the RETURN parameter as you have done and use the ExecuteNonQuery method.
>>Simply, I want to knowis my stored procedure is successful, True or False?<<
There are a couple of ways this can happen. If for example, you violate a constraint then an automatic exception will be raised, which you can trap in your .NET app using Try ... Catch. If on the other hand you want to set a return value then, as I stated previously, you have to use the ExecuteNonQuery method. You can also trigger an exception from within your stored procedure by using RAISERROR. But without seeing the Stored Procedure it is difficult to be more precise.
One other option you have with SQL Server 2005 & 2008 is that you can place your code in the Stored Procedure within a Try ... Catch blocks. This way you can trap the error in your Stored Procedure and handle it, as opposed to an automtic exception been raised.
There are a couple of ways this can happen. If for example, you violate a constraint then an automatic exception will be raised, which you can trap in your .NET app using Try ... Catch. If on the other hand you want to set a return value then, as I stated previously, you have to use the ExecuteNonQuery method. You can also trigger an exception from within your stored procedure by using RAISERROR. But without seeing the Stored Procedure it is difficult to be more precise.
One other option you have with SQL Server 2005 & 2008 is that you can place your code in the Stored Procedure within a Try ... Catch blocks. This way you can trap the error in your Stored Procedure and handle it, as opposed to an automtic exception been raised.
After re-reading your comments again. I see to separate threads going on:
1. How do you return output parameters to .NET. And in particular the RETURN parameter.
2. How do you execute a number of statements and ROLLBACK if any fail.
Is this accurately summarized and if so are you still having difficulty with either in particular?
1. How do you return output parameters to .NET. And in particular the RETURN parameter.
2. How do you execute a number of statements and ROLLBACK if any fail.
Is this accurately summarized and if so are you still having difficulty with either in particular?
ASKER
Probably #1 because the stored procedure and rollback code work fine. And I actually already have the Try /Catch in the .NET code. Logically, with ExecuteNonQuery I thought I could return the 'saveerror' value to the .NET code from the stored procedure based on the return @saveerror line specifically when there is a rollback, but even if I set @saveerror to a static value 9999 it is -1 or 2 or whatever to the .NET code.
line:
Here's a snippet of the stored Procedure:
....etc......
IF @saveerror = 0
begin
UPDATE tblWrapUpComments SET tblWrapUpComments.UserID = @New_User WHERE UserID = @Old_User
Set @saveerror = @@ERROR
end
IF @saveerror = 0
begin
DELETE from tblUsers WHERE UserID = @Old_User
Set @saveerror = @@ERROR
end
IF @saveerror = 0
COMMIT
ELSE
ROLLBACK
END
return @saveerror
line:
Here's a snippet of the stored Procedure:
....etc......
IF @saveerror = 0
begin
UPDATE tblWrapUpComments SET tblWrapUpComments.UserID = @New_User WHERE UserID = @Old_User
Set @saveerror = @@ERROR
end
IF @saveerror = 0
begin
DELETE from tblUsers WHERE UserID = @Old_User
Set @saveerror = @@ERROR
end
IF @saveerror = 0
COMMIT
ELSE
ROLLBACK
END
return @saveerror
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>because the stored procedure and rollback code work fine<<
You really need to look into using Try ... Catch in your Stored Procedure
BEGIN TRY
UPDATE tblWrapUpComments SET tblWrapUpComments.UserID = @New_User WHERE UserID = @Old_User
DELETE from tblUsers WHERE UserID = @Old_User
END TRY
BEGIN CATCH
Set @saveerror = ERROR_NUMBER()
ROLLBACK
Return @saveerror
END CATCH
COMMIT
Return
Or at the very least do something like we had to do prior to SQL Server 2005:
UPDATE tblWrapUpComments SET tblWrapUpComments.UserID = @New_User WHERE UserID = @Old_User
Set @saveerror = @@ERROR
GoTo ErrHandler
DELETE from tblUsers WHERE UserID = @Old_User
Set @saveerror = @@ERROR
GoTo ErrHandler
COMMIT
Return
ErrHandler:
ROLLBACK
return @saveerror
You really need to look into using Try ... Catch in your Stored Procedure
BEGIN TRY
UPDATE tblWrapUpComments SET tblWrapUpComments.UserID = @New_User WHERE UserID = @Old_User
DELETE from tblUsers WHERE UserID = @Old_User
END TRY
BEGIN CATCH
Set @saveerror = ERROR_NUMBER()
ROLLBACK
Return @saveerror
END CATCH
COMMIT
Return
Or at the very least do something like we had to do prior to SQL Server 2005:
UPDATE tblWrapUpComments SET tblWrapUpComments.UserID = @New_User WHERE UserID = @Old_User
Set @saveerror = @@ERROR
GoTo ErrHandler
DELETE from tblUsers WHERE UserID = @Old_User
Set @saveerror = @@ERROR
GoTo ErrHandler
COMMIT
Return
ErrHandler:
ROLLBACK
return @saveerror
http://www.sqlhub.com/2009/04/stored-procedure-return-value-handle.html