Stored procedure not working on code

Question

I've got a really simple stored procedure that returns to me some output variables.

If I execute this stored procedure by any method (Management Studio, Visual Studio Server Explorer), it works pretty fine.

The thing is, if I try to execute it with ExecuteNonQuery() on my client side, it doesn't execute the stored procedure, the ExecuteNonQuery() returns the value -1 and there are no outputs. I want to know if I should be using something other than ExecuteNonQuery() to execute this procedure and get to get these output variables.

This is my stored procedure:

CREATE PROCEDURE [dbo].ChekEmail
    @Email varchar(50),
    @Id int output,
    @Flag_User int output
AS
    SET @Id = 0
    SET @Id = (SELECT Client.Id FROM Client WHERE Client.Email = @Email)

    IF(@Id > 0)
    BEGIN 
        SET @Flag_User = 1
        RETURN @Id
    END
    ELSE
    BEGIN 
        SET @Id = (SELECT Clinic.Id FROM Clinic WHERE Clinic.Email = @Email)

        IF(@Id > 0)
        BEGIN 
            SET @Flag_User = 2
            RETURN @Id
        END
    END

and this is where I execute it

public int CheckEmail(string Email)
{
    Conection con = new Conection();

    SqlCommand cmd = new SqlCommand("ChekEmail");
    cmd.Connection = con.connect();

    cmd.Parameters.AddWithValue("@Email", Email);
    cmd.Parameters.Add("@Id", SqlDbType.Int).Direction = ParameterDirection.Output;
    cmd.Parameters.Add("@Flag_User", SqlDbType.Int).Direction = ParameterDirection.Output;

    cmd.CommandType = CommandType.StoredProcedure;

    int x = cmd.ExecuteNonQuery();

    if (x >= 0)
    {
        int Id_Owner = Convert.ToInt32(cmd.Parameters["@Id"].Value.ToString());
        int Flag_User = Convert.ToInt32(cmd.Parameters["@Flag_User"].Value.ToString());

        if (Flag_User == 1)
        {
            return Id_Owner;
        }
        else
        {
            return 0;
        }
    }
    else
    {
        return 0;
    }
}

Answer 1

As you can read on MSDN page of ExecuteNonQuery returning -1 for a SELECT query is a documented behavior

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1

But this doesn't mean that your stored procedure has not been executed. Simply there is no return of the rows affected in your current stored procedure because you don't use one of the required commands but just a SELECT.
Don't mistake the RETURN @Id as the value returned by the ExecuteNonQuery. You should simply remove the if after the ExecuteNonQuery call

Also you could change your stored procedure to

CREATE PROCEDURE [dbo].ChekEmail
    @Email varchar(50),
    @Id int output,
    @Flag_User int output
AS
    SET @Id = 0
    SELECT @Id = Client.Id FROM Client WHERE Client.Email = @Email
    IF(@Id >0 )
    BEGIN 
        SET @Flag_User = 1
        RETURN @Id
    END
    ELSE
    BEGIN 
        SELECT @Id = Clinic.Id FROM Clinic WHERE Clinic.Email = @Email
        IF(@Id >0 )
        BEGIN 
            SET @Flag_User = 2
            RETURN @Id
        END
    END

In this updated stored procedure I use the non standard SELECT to assign the @ID paramenter the result of the query. The reason for this change is explained well in this Answer (Otherwise you need to check for a NULL value returned in the @Id parameter)

And if you want to read the RETURN @Id you need to add another parameter to your query marked with ParameterDirection.ReturnValue.

 cmd.Parameters.Add("@result", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
 ....

 int result = Convert.ToInt32(cmd.Parameters["@result"].Value.ToString());