[alykins]

sql

create proc usp_myDemo
    @Surname varchar()
AS

SELECT [fieldA], [fieldB], ... [fieldN]
FROM tbl_Demo
WHERE [SURNAME] = @Surname

somewhere in aspx page

<asp:TextBox runat="server" ID=txtSur></asp:TextBox>
............
<asp:GridView runat="server" ID=gr></asp:GridView>

somewhere in C#

DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(cnstr);
using(conn)
{
  SqlCommand cmd = new SqlCommand("usp_myDemo", conn);
  using(cmd)
  {
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add(new SqlParameter("@Surname", txtSur.Text));
    // note this string is not cleaned- you need to clean it first 
    conn.Open();
    SqlDataReader dr = cmd.ExecuteReader();
    dt.Load(dr);
    conn.Close();
   }
}

gr.DataSource = dt;
gr.DataBind();