title: SQL Server Procedure and a Record set
body: <p>I want to execute a stored procedure in SQL Server, then look at the results to see whether any have returned. Here is my c#:</p>
  
  <pre><code>public ActionResult LogOn(LogOnModel model, string returnUrl)
  {
          SqlConnection conn = null;
          conn = new SqlConnection("Server=(local);Database=holidaydatabase;User Id=holidaylogin;Password=test");
  
          SqlCommand command = new SqlCommand("EmployeeValidation", conn);
          command.CommandType = CommandType.StoredProcedure;
          command.Parameters.Add("@Username", SqlDbType.VarChar).Value = model.UserName;
          command.Parameters.Add("@Password", SqlDbType.VarBinary).Value =model.Password;
  
          conn.Open();
          command.ExecuteNonQuery();
  
          conn.Close();
  }
  </code></pre>
  
  <p>This is the SQL Server stored procedure: </p>
  
  <pre><code>DECLARE @EmployeeID int,
      @LinemanagerID int,
      @EmployeeJobRoleID int
  
  SELECT  @EmployeeID = E.ID,
      @LinemanagerID = E.LineManagerID,
      @EmployeeJobRoleID = E.Job_RoleID
  FROM    Employees E
  WHERE   E.Username = @Username and
      E.Password = @Password
  
  SELECT  E.First_name + ' ' + E.Second_Name as Full_name,
      J.Name,
      E.Username,
      J.MaxUsersOffPerDay,
      E.ID,
      E.LineManagerEligibility,
      E.Date_Joind_Company,
      J.Name,
      J.MaxUsersOffPerDay
  FROM    Employees E
      INNER JOIN JobRole J ON E.Job_RoleID = J.ID 
  WHERE   Username = @Username
  AND [Password] = @Password
  
  SELECT  E.Username
  FROM    Employees E
  WHERE   E.ID = @LinemanagerID
  
  SELECT  [Values]
  FROM    HolidayValues        
  
  SELECT  E.First_name + ' ' + E.Second_Name
  FROM    Employees E
  WHERE   E.ID = @LinemanagerID
  </code></pre>
  
  <p>How can I look through these selects and assign variables to the results?</p>