(newbie) Get the return value from a stored proc (return @aValue)

[SoftLion]

Hi,
i'm a newbie to ADO.NET and would like to know how to get the return value
of a stored procedure called using ExecuteNonQuery ?

Thks

 

[CT]

Here's one way of getting the return value of a SQL Server stored procedure:

Dim returnValueConnection As SqlConnection
Dim returnValueCommand As SqlCommand
Dim returnValueParameter As SqlParameter
Dim returnValue As Object

' Instantiate and initialize command
returnValueCommand = New SqlCommand("RETURN_VALUE",
returnValueConnection)
returnValueCommand.CommandType = CommandType.StoredProcedure
' Instantiate, initialize and add parameter to command
returnValueParameter =
returnValueCommand.Parameters.Add("@RETURN_VALUE", _
SqlDbType.Int)
' Indicate this is a return value parameter
returnValueParameter.Direction = ParameterDirection.ReturnValue
' Get RETURN_VALUE
returnValue = returnValueCommand.ExecuteScalar()

 

[William Ryan eMVP]

SoftLion:

In addition to Carsten's excellent post, you may want to check out Bill
Vaughn's article Retrieving the Gozoutas at www.betav.com -> Articles ->
MSDN which discusses return values and Output params.

HTH,

Bill
www.devbuzz.com
www.knowdotnet.com

 

[Julian Crofton]

The following code works with the stored procedure that follows it. The
trick is to define output parameters in the stored procedure and pick them
out of the parameters collection after the procedure has been executed but
before the connection is closed. The only reliable way I have found of
creating a working SQL command object is to drag the stored procedure from
Server Explorer onto a component's design page.

Regards,

Julian

-----------------------------

SqlConnection1.Open()
SQLCommand1.Parameters("@UserID").Value = UserID
SQLCommand1.ExecuteNonQuery()
myUsername = scGetMessage.Parameters("@Username").Value
SqlConnection1.Close()

-----------------------------

ALTER PROCEDURE dbo.mysp_GetUsername
(
@UserID int,
@Username nvarchar(30) OUTPUT
)
AS

SET NOCOUNT ON;

SELECT @Username = Username
FROM Users
WHERE UserID = @UserID