Obtaining the return code from a stored procedure

[Scott Natwick]

Hi,

Is there a way to obtain the return code from a stored procedure?

Here is what I have so far. The procedure executes, but I'm not able to
find the return code from the procedure.

Thanks,
Scott

SqlConnection sqlConn = new SqlConnection(strDbConn);
SqlCommand sqlCmd = new SqlCommand("usp_proc_name", sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;

SqlParameter param1 = new SqlParameter("@PARAM_1", SqlDbType.VarChar, 100);
param1.Value = field1.Text;
sqlCmd.Parameters.Add(param1);

sqlCmd.Connection.Open();
SqlDataReader sqlReader =
sqlCmd.ExecuteReader(CommandBehavior.CloseConnecti on);
if (sqlReader.Read())
{
if (sqlReader.GetInt32(0) == 0)
{
// Procedure successul (expected)
}
else
{
// Procedure error occured (expected)
}
}
else
{
// Procedure error occured (expected)
** The debugger always lands here as I'm not doing something right **
}

CREATE PROCEDURE [dbo].[usp_proc_name]
@PARAM_1 varchar(100)
AS

INSERT INTO [dbo].[table]
(
...
)
VALUES
(
...
)

SELECT @error_num = @@ERROR

RETURN @error_num
GO

[Greg Burns]

Yes you can get the return value from sproc.

sqlCmd.Parameters.Add(New SqlParameter("RETURN_VALUE",
SqlDbType.Int)).Direction = ParameterDirection.ReturnValue

The name of the parameter is not critacal (notice I am not even using the @
character).

Pretty sure you cannot read the value back (any parameters for that matter)
until the datareader is closed!

Dim i as integer = CType(sqlCmd.Parameters("RETURN_VALUE").Value, Integer)

Looking at your sproc, you don't even want to use a datareader here. Try
this instead:

Dim cn As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("C onnectionString"))
Dim cmd As SqlCommand = New SqlCommand("dbo.usp_SomeSproc", cn)
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add(New SqlParameter("@SomeID",
SqlDbType.Int)).Value = someID
cmd.Parameters.Add(New SqlParameter("RETURN_VALUE",
SqlDbType.Int)).Direction = ParameterDirection.ReturnValue

Try
cn.Open()
cmd.ExecuteNonQuery()
Catch ex As SqlException
Throw New Exception("some error message", ex)
Finally
cn.Close()
End Try

Dim SomeReturnValue As Integer =
CType(cmd.Parameters("RETURN_VALUE").Value, Integer)

HTH,
Greg

"Scott Natwick" <no**********@yahoo.com> wrote in message
news:dP********************@comcast.com...

Hi,

Is there a way to obtain the return code from a stored procedure?

Here is what I have so far. The procedure executes, but I'm not able to
find the return code from the procedure.

Thanks,
Scott

SqlConnection sqlConn = new SqlConnection(strDbConn);
SqlCommand sqlCmd = new SqlCommand("usp_proc_name", sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;

SqlParameter param1 = new SqlParameter("@PARAM_1", SqlDbType.VarChar,
100);
param1.Value = field1.Text;
sqlCmd.Parameters.Add(param1);

sqlCmd.Connection.Open();
SqlDataReader sqlReader =
sqlCmd.ExecuteReader(CommandBehavior.CloseConnecti on);
if (sqlReader.Read())
{
if (sqlReader.GetInt32(0) == 0)
{
// Procedure successul (expected)
}
else
{
// Procedure error occured (expected)
}
}
else
{
// Procedure error occured (expected)
** The debugger always lands here as I'm not doing something right **
}

CREATE PROCEDURE [dbo].[usp_proc_name]
@PARAM_1 varchar(100)
AS

INSERT INTO [dbo].[table]
(
...
)
VALUES
(
...
)

SELECT @error_num = @@ERROR

RETURN @error_num
GO

[Scott Natwick]

Greg,

Your suggestion works perfectly! Here is how I modified the code:

try
{
sqlCmd.Connection.Open();
sqlCmd.ExecuteNonQuery();
}
catch (Exception objException)
{
...
}
finally
{
sqlCmd.Connection.Close();
}
int nReturnCode = (int)sqlCmd.Parameters["@R_RETURN_CODE"].Value;

Thanks again!
Scott Natwick

"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:eL*************@tk2msftngp13.phx.gbl...

Yes you can get the return value from sproc.

sqlCmd.Parameters.Add(New SqlParameter("RETURN_VALUE",
SqlDbType.Int)).Direction = ParameterDirection.ReturnValue

The name of the parameter is not critacal (notice I am not even using the
@ character).

Pretty sure you cannot read the value back (any parameters for that
matter) until the datareader is closed!

Dim i as integer = CType(sqlCmd.Parameters("RETURN_VALUE").Value, Integer)

Looking at your sproc, you don't even want to use a datareader here. Try
this instead:

Dim cn As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("C onnectionString"))
Dim cmd As SqlCommand = New SqlCommand("dbo.usp_SomeSproc", cn)
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add(New SqlParameter("@SomeID",
SqlDbType.Int)).Value = someID
cmd.Parameters.Add(New SqlParameter("RETURN_VALUE",
SqlDbType.Int)).Direction = ParameterDirection.ReturnValue

Try
cn.Open()
cmd.ExecuteNonQuery()
Catch ex As SqlException
Throw New Exception("some error message", ex)
Finally
cn.Close()
End Try

Dim SomeReturnValue As Integer =
CType(cmd.Parameters("RETURN_VALUE").Value, Integer)

HTH,
Greg

"Scott Natwick" <no**********@yahoo.com> wrote in message
news:dP********************@comcast.com...

Hi,

Is there a way to obtain the return code from a stored procedure?

Here is what I have so far. The procedure executes, but I'm not able to
find the return code from the procedure.

Thanks,
Scott

SqlConnection sqlConn = new SqlConnection(strDbConn);
SqlCommand sqlCmd = new SqlCommand("usp_proc_name", sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;

SqlParameter param1 = new SqlParameter("@PARAM_1", SqlDbType.VarChar,
100);
param1.Value = field1.Text;
sqlCmd.Parameters.Add(param1);

sqlCmd.Connection.Open();
SqlDataReader sqlReader =
sqlCmd.ExecuteReader(CommandBehavior.CloseConnecti on);
if (sqlReader.Read())
{
if (sqlReader.GetInt32(0) == 0)
{
// Procedure successul (expected)
}
else
{
// Procedure error occured (expected)
}
}
else
{
// Procedure error occured (expected)
** The debugger always lands here as I'm not doing something right **
}

CREATE PROCEDURE [dbo].[usp_proc_name]
@PARAM_1 varchar(100)
AS

INSERT INTO [dbo].[table]
(
...
)
VALUES
(
...
)

SELECT @error_num = @@ERROR

RETURN @error_num
GO