Error In Stored Procedure Sql Statement

I am using C# to pass few paramenters to build the SQL statement in the stored procedure but when i run it i get this error:

Could not find stored procedure 'SELECT file_no, old_file_no, id_number, person_name FROM persons WHERE gender IN (null, 'MALE', 'FEMALE', 'OTHER') ORDER BY NAME'.

though the stored procedure dose exist and i don't have any problem when i staticaly write the sql statement in the stored procedure. can you pleas help..

here is my store dprocedure:



Code Block
USE shefa
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[sp_get_all_patients]
@order_field varchar(255),
@patient_gender varchar(255)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql_statement varchar(255)

SET @sql_statement = 'SELECT file_no, old_file_no, id_number, person_name FROM persons ' + @patient_gender + ' ORDER BY ' + @order_field
EXEC @sql_statement
-- IF @order_field = 'FILE NO'
-- SELECT file_no, old_file_no, id_number, person_name FROM persons ORDER BY file_no
-- ELSE IF @order_field = 'OLD FILE NO'
-- SELECT file_no, old_file_no, id_number, person_name FROM persons ORDER BY old_file_no
-- ELSE
-- SELECT file_no, old_file_no, id_number, person_name FROM persons ORDER BY person_name
END







and here is my C#:




Code Block
private void get_all_patients(string order_field, string patient_gender)
{
this.Cursor = Cursors.WaitCursor;
data_table = new DataTable();
// sql_connection = new SqlConnection("Data Source=.\SQLEXPRESS;initial catalog=shefa;integrated security=true");
sql_connection = new SqlConnection(public_var.sql_connection);
sql_command = new SqlCommand("sp_get_all_patients", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_command.Parameters.Add("@order_field", SqlDbType.VarChar).Value = order_field;

if (patient_gender == "ALL")
sql_command.Parameters.Add("@patient_gender", SqlDbType.VarChar).Value = "WHERE gender IN (null, 'MALE', 'FEMALE', 'OTHER'";
else if (patient_gender == "MALE ONLY")
sql_command.Parameters.Add("@patient_gender", SqlDbType.VarChar).Value = "WHERE gender ='MALE'";
else if (patient_gender == "FEMALE ONLY")
sql_command.Parameters.Add("@patient_gender", SqlDbType.VarChar).Value = "WHERE gender ='FEMALE'";
else if (patient_gender == "OTHER")
sql_command.Parameters.Add("@patient_gender", SqlDbType.VarChar).Value = "WHERE gender NOT IN ('MALE', 'FEMALE')";
else
sql_command.Parameters.Add("@patient_gender", SqlDbType.VarChar).Value = "WHERE gender IN (null, 'MALE', 'FEMALE', 'OTHER')";
data_adapter = new SqlDataAdapter(sql_command);
data_adapter.Fill(data_table);
dataPatients.DataSource = data_table;

dataPatients.Columns["file_no"].HeaderText = "File no";
dataPatients.Columns["file_no"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopRight;
dataPatients.Columns["file_no"].Width = 100;
dataPatients.Columns["old_file_no"].HeaderText = "OLD File no";
dataPatients.Columns["old_file_no"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopRight;
dataPatients.Columns["old_file_no"].Width = 100;
dataPatients.Columns["old_file_no"].Visible = false;
dataPatients.Columns["id_number"].HeaderText = "ID Number";
dataPatients.Columns["id_number"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopRight;
dataPatients.Columns["id_number"].Width = 100;
dataPatients.Columns["person_name"].HeaderText = "Name";
dataPatients.Columns["person_name"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft;
dataPatients.Columns["person_name"].Width = 650;
this.Cursor = Cursors.Default;
}