title: using two data tables in a procedure SQL
body: <p>I was put in a situation where I was forced to create a procedure that uses two data tables such as this:</p>
  
  <pre><code>ALTER PROCEDURE [sesuser].[Login_GetUserList]
  (
      @beginsWith NVARCHAR(20) = NULL,
      @SortBy nvarchar(20) = NULL,
      @sortByDirection nvarchar(5) = N'ASC'
  )
  AS
  BEGIN
  
      DECLARE @searchStr NVARCHAR(21)
  
      IF @beginsWith IS NULL
          SET @beginsWith = N''
      ELSE
          SET @beginsWith = dbo.SuperTrim(@beginsWith);
  
      IF LEN(@beginsWith) &gt; 0
          SET @searchStr = @beginsWith + N'%'
      ELSE
          SET @searchStr = N'%'
  
  DECLARE @sqlSTR NVARCHAR(4000)
  DECLARE @sqlOffice NVARCHAR(100)
  DECLARE @sqlOfficeID NVARCHAR(10)
  DECLARE @sqlEndSTR NVARCHAR(4000)
  
  SET @sqlSTR = N' SELECT 
          [SESLoginID] AS LoginID
          ,[SESSuspended] AS LoginSuspended
          ,[SESAdmin] AS Role_Admin
          ,[SESLoginID]
          ,[SESFirstName]
          ,[SESLastName]
          ,[SESEmail]
          ,[SESSuspended]
          FROM sesuser.SESLogin
          WHERE SESFirstName LIKE ''' + @searchStr + ''''
  
  SET @sqlOfficeID = N' SELECT 
          [SESLoginID]
          FROM sesuser.SESLogin
          WHERE SESFirstName LIKE ''' + @searchStr + ''''
  
  SET @sqlOffice = N' SELECT
          [OfficeName]
          FROM sesuser.Office
          WHERE OfficeID LIKE ''' + @sqlOfficeID + ''''
  
  SET @sqlEndSTR = @sqlSTR + @sqlOffice
  
  PRINT @sqlEndSTR
  
  EXEC sp_ExecuteSQL @sqlEndSTR
  
  END
  </code></pre>
  
  <p>so as I understand it, this code supposed to in a table of office IDs and equivalent Office Name to replace the office ID with a Name in the other table and return it.</p>
  
  <p>I then use the retrieve data string in a method in my c# code:</p>
  
  <pre><code>public static DataTable GetUserList(string beginsWith, out string errMessage, string sortBy, string sortByDirection)
  {
     DataTable dt = null;
     int errNum = 0;
     string sql = "[sesuser].[Login_GetUserList]";
     SqlDataAdapter adap = null;
  
     SqlParameter param = null;
  
     errMessage = "";
     SqlConnection conn = Functions.Database.DBConnect(out errNum);
  
     try
     {
        SqlCommand cmd = new SqlCommand(sql);
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
  
        param = new SqlParameter("@beginsWith", SqlDbType.NVarChar, 40);
        param.Value = string.IsNullOrEmpty(beginsWith) ? "" : beginsWith.Trim();
        cmd.Parameters.Add(param);
        cmd.Parameters.AddWithValue("@SortBy", sortBy);
        cmd.Parameters.AddWithValue("@sortByDirection", sortByDirection);
  
        adap = new SqlDataAdapter(cmd);
  
        dt = new DataTable();
        adap.Fill(dt);
     }
     catch (Exception ex)
     {
        errMessage = ex.Message;
     }
     finally
     {
        Functions.Database.DBClose(conn);
     }
  
     return dt;
  }
  </code></pre>
  
  <p>and later in my asp.net code I call back office name</p>
  
  <pre><code>&lt;asp:BoundField DataField="OfficeName" HeaderText="Business Unit" ReadOnly="True"  /&gt;
  </code></pre>
  
  <p>The problem is, it errors, saying that it cant find <code>OfficeName</code>.</p>
  
  <p>Is there something I forgot? Or am I doing it wrong?</p>