Capturing return value from SQL Server's stored procedure

[Guest]

Using Microsoft.Practices.EnterpriseLibrary.Data, how do I capture the return
value from executing a stored procedure on SQL Server 2000 (i.e., how could
I tell the Stored Procedure did not return 0 [indicating failure] or that a
transaction the stored procedure begun had been rolled back):

No exceptions were thrown from the following when -1 was returned and
transaction rolled back:
Database db = DatabaseFactory.CreateDatabase();

string sqlCommand = "uspCreator";
DBCommandWrapper creatorCommandWrapper =
db.GetStoredProcCommandWrapper(sqlCommand);

db.ExecuteNonQuery(creatorCommandWrapper);

 

[David Browne]

Using Microsoft.Practices.EnterpriseLibrary.Data, how do I capture the
return
value from executing a stored procedure on SQL Server 2000 (i.e., how
could
I tell the Stored Procedure did not return 0 [indicating failure] or that
a
transaction the stored procedure begun had been rolled back):

No exceptions were thrown from the following when -1 was returned and
transaction rolled back:
Database db = DatabaseFactory.CreateDatabase();

string sqlCommand = "uspCreator";
DBCommandWrapper creatorCommandWrapper =
db.GetStoredProcCommandWrapper(sqlCommand);

db.ExecuteNonQuery(creatorCommandWrapper);

Checking the return code of stored procedures is for server side invocation,
and is simply a workaround for the fact that when a stored procedure calls
another stored procedure, it cannot capture the error message. Client code
should detect failure from the error message.

So the stored procedure should indicate failure with both a return code, and
an error message. If no error message is currently being generated on
failure, then you should generate one using RAISERROR in the stored
procedure.

David

 

[Kevin Yu [MSFT]]

Hi Patrick,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to get the return value from
the executing stored procedure. If there is any misunderstanding, please
feel free to let me know.

You can add another SqlParameter object to the parameter collection and set
its Direction property to ParameterDirection.ReturnValue before executing.
After execution, you can check the value of this parameter to get the
return value.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."