CODEWITHSUNDEEP
c#sql-serverstored-procedures
Ana Pimentel
Ana Pimentel

Reputation: 111

How to get the return value of a stored procedure?

I have the following stored procedure and I want to obtain the value that it returns:

ALTER PROCEDURE [dbo].[ExistsItemID]
    @ItemID uniqueidentifier
AS
BEGIN
    IF (EXISTS (SELECT ItemID FROM Discounts WHERE ItemID = @ItemID))
    BEGIN
        RETURN 1
    END
    ELSE 
    BEGIN
        RETURN 0
    END
END

And in C# I have the following code:

using (SqlConnection sqlCon = new SqlConnection(scheduleConnection.ConnectionString))
{
    sqlCon.Open();

    SqlCommand sqlCmd = new SqlCommand("ExistsItemID", scheduleConnection);
    sqlCmd.CommandType = CommandType.StoredProcedure;

    sqlCmd.ExecuteScalar();

    SqlParameter returno = new SqlParameter();
    returno.Direction = ParameterDirection.ReturnValue;
    int valor = returno.Value;
}

But it has not worked for me, how can I get the value of the stored procedure? First of all, thanks

Upvotes: 1

Views: 2233

Answers (1)

Paul Alan Taylor
Paul Alan Taylor

Reputation: 10680

You need to add the parameter to the command before executing it. Use the Add method on the Parameters collection.

using (SqlConnection sqlCon = new SqlConnection(scheduleConnection.ConnectionString))
{
    sqlCon.Open();

    SqlCommand sqlCmd = new SqlCommand("ExistsItemID", scheduleConnection);
    sqlCmd.CommandType = CommandType.StoredProcedure;

    SqlParameter returno = new SqlParameter();
    returno.Direction = ParameterDirection.ReturnValue;

    sqlCmd.Parameters.Add(returno);

    sqlCmd.ExecuteScalar();
                        
    int valor = returno.Value;
}

Upvotes: 1

Related Questions