getting the return value from a stored procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have a stored procedure that returns a value but does not select any rows as shown below
How can I access the value of this return value from the database using datasets or dataadapters.

Thanx in advance

Geri

:procedure in question
CREATE PROCEDURE SPSelectUserID as

DECLARE @USERNAME varchar(50)
DECLARE @USERID int

EXEC SPSelectUserLogin @USERNAME = @USERNAME OUTPUT
SELECT @USERID = UserID FROM JobControl WHERE UserName = @USERNAME

RETURN @USERID
 
Geraldine,
How can I access the value of this return value from the database using
Click to expand...
datasets or dataadapters.
Short answer: You don't.

Long answer: You need to use the Command object of the respective client you
are using.

Something like (untested):
Dim command As New SqlClient.SqlCommand("SPSelectUserID",
theConnection)
Dim parm As SqlClient.SqlParameter
parm = command.Parameters.Add("@ReturnValue", SqlDbType.Int)
parm.Direction = ParameterDirection.ReturnValue
command.Parameters.Add("@USERNAME", SqlDbType.VarChar, 50)

command.ExecuteNonQuery()

Dim userid As Integer = DirectCast(parm.Value, Integer)

Hope this helps
Jay
 
Geraldine Hobley said:
Hello,
I have a stored procedure that returns a value but does not
select any rows as shown below
How can I access the value of this return value from the database
using datasets or dataadapters.

[...]
Click to expand...

Doesn't seem to be a VB.NET language problem. Please turn to
microsoft.public.dotnet.framework.adonet
 
Back
Top