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

  • Thread starter Thread starter SoftLion
  • Start date Start date
S

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
 
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()
 
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
 
Back
Top