SQL select query for creating a stored procedure doesn't work in asp.net

Question

I am trying to make a stored procedure in Visual Studio using C# asp.net. The procedure is this:

    SqlConnection connection = new SqlConnection(...);
    SqlCommand command = new SqlCommand();

    try
    {
        connection.Open();
        command.CommandType = CommandType.Text;
        command.CommandText = "CREATE PROCEDURE NumberOfPatientsWithAChosenDiagnosis(@diagnosis VARCHAR, @numberOfPatients INT OUTPUT) AS SELECT * FROM Patients WHERE diagnosis = '@diagnosis' " + "SELECT @numberOfPatients = COUNT(*) FROM Patients WHERE diagnosis = '@diagnosis'";
        command.Connection = connection;
        command.ExecuteNonQuery();
        tb_procedure.Text = "The procedure \"NumberOfPatientsWithAChosenDiagnosis\" was created with success!";
    }
    catch (Exception ex)
    {
        tb_procedure.Text = "Error at creating the procedure!\n" + ex.Message;
    }
    finally
    {
        connection.Close();
    }

I have a DropDownList in which there are all the diagnoses from the table. The problem is that when i test the procedure with a chosen diagnosis from the DropDownList, it shows me 0 results, even though there are records with the chosen diagnosis. If I replace the parameter '@diagnosis' with 'flu' for example, the procedure works perfectly and it returns me the exact number of patients with flu. What am I writing wrong? Thank you!

Answer 1

You are defining the parameter @diagnosis with only VARCHAR type and missing to define its size. Without the size it is assumed that you want a VARCHAR(1) and thus only one char will be set in the parameter.

So everytime you call the stored procedure and try to set the @diagnosis parameter with a string like 'flu' only the letter 'f' is received by the SP. And you will find nothing because there is no diagnosis defined with the single 'f' letter.

Just add the size when you create that parameter

command.CommandText = @"CREATE PROCEDURE NumberOfPatientsWithAChosenDiagnosis
       (@diagnosis VARCHAR(100), .....

You have also another error in your sql code. You put the parameter between single quotes. This is a literal string, it is no more a parameter placeholder.
You need to remove the single quotes around the place where you use the @diagnosis parameter. Finally, if you want to just count the number of patients with the given diagnosis then there is no need for the first sql SELECT.

command.CommandText = @"CREATE PROCEDURE NumberOfPatientsWithAChosenDiagnosis
            (@diagnosis VARCHAR(100), @numberOfPatients INT OUTPUT) 
            AS 
            -- Not needed ==> SELECT * FROM Patients WHERE diagnosis = @diagnosis;
            SELECT @numberOfPatients = COUNT(*) 
            FROM Patients 
            WHERE diagnosis = @diagnosis";