using two data tables in a procedure SQL

I was put in a situation where I was forced to create a procedure that uses two data tables such as this:

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) > 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

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.

I then use the retrieve data string in a method in my c# 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;
}

and later in my asp.net code I call back office name

<asp:BoundField DataField="OfficeName" HeaderText="Business Unit" ReadOnly="True"  />

The problem is, it errors, saying that it cant find OfficeName.

Is there something I forgot? Or am I doing it wrong?

Looks like your stored procedure return two subsets.

Amend your stored procedure like so:

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) > 0
        SET @searchStr = @beginsWith + N'%'
    ELSE
        SET @searchStr = N'%'    
DECLARE @sqlSTR NVARCHAR(4000)    
SET @sqlSTR = N' SELECT 
         [OfficeName]
        ,[SESLoginID] AS LoginID
        ,[SESSuspended] AS LoginSuspended
        ,[SESAdmin] AS Role_Admin
        ,[SESLoginID]
        ,[SESFirstName]
        ,[SESLastName]
        ,[SESEmail]
        ,[SESSuspended]
        FROM sesuser.SESLogin
        INNER JOIN sesuser.Office
        ON sesuser.Office.OfficeID = sesuser.SESLogin.SESLoginID
        WHERE SESFirstName LIKE ''' + @searchStr + ''''    
PRINT @sqlSTR    
EXEC sp_ExecuteSQL @sqlSTR    
END

I dont understand why the query is in a query string, surely it would work better

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) > 0
    SET @searchStr = @beginsWith + N'%'
ELSE
    SET @searchStr = N'%'    

SELECT  [OfficeName]
    ,[SESLoginID] AS LoginID
    ,[SESSuspended] AS LoginSuspended
    ,[SESAdmin] AS Role_Admin
    ,[SESLoginID]
    ,[SESFirstName]
    ,[SESLastName]
    ,[SESEmail]
    ,[SESSuspended]
    FROM sesuser.SESLogin
    INNER JOIN sesuser.Office
    ON sesuser.Office.OfficeID = sesuser.SESLogin.SESLoginID
    WHERE SESFirstName LIKE @searchStr 

END