CODEWITHSUNDEEP
c#asp.netsqlstored-proceduresado.net
connersz
connersz

Reputation: 1213

How do I get an SP return value?

I have done it before but just can't get it to work, I only want to do a select within my SP that returns whatever, then execute in the code-behind and then do whatever with the code returned.

The C# code

protected long getPassword(string EnteredPass) 
{
    var connectionString = ConfigurationManager.ConnectionStrings["GetConnector"].ConnectionString;
    SqlConnection dbConnection = new SqlConnection();
    dbConnection.ConnectionString = connectionString;
    SqlCommand dbCommand = new SqlCommand("PL_User_Check", dbConnection);
    dbCommand.CommandType = CommandType.StoredProcedure;

    SqlParameter abc = dbCommand.Parameters.Add("@User", SqlDbType.VarChar);
    abc.Value = EnteredPass;
    dbCommand.Parameters.Add("@ReturnValue", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue;

    dbConnection.Open();

    dbCommand.ExecuteNonQuery();
    dbConnection.Close();
    object x = dbCommand.Parameters[""].Value;
    return Convert.ToInt64(x);
}

Stored procedure...

ALTER PROCEDURE [dbo].[PL_User_Check]
    @User VARCHAR(50),
    @ReturnValue BIGINT OUTPUT
AS

DECLARE @pass BIGINT

--Data
SET @Pass = (SELECT Pass
    FROM dbo.UserPasswords
    WHERE [User] = @User)

SET @ReturnValue = @Pass

Upvotes: 1

Views: 987

Answers (3)

Konstantin
Konstantin

Reputation: 3294

before execute

dbCommand.AddOutParameter("@ReturnValue", DbType.Int64);

after execute

dbCommand.GetParameterValue<Int64>("@ReturnValue")

Upvotes: 0

Gregory A Beamer
Gregory A Beamer

Reputation: 17010

Pass the parameter in explicitly and then you can query the parameter object for its value. You are using Parameters.Add and implicitly creating the object. Method:

  1. Create parameter object for return value
  2. Add to the dbCommand explicitly

That should solve your problem.

Upvotes: 0

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

Use:

ParameterDirection.Output

instead of:

ParameterDirection.ReturnValue

Upvotes: 2

Related Questions