Stored Procedure Returns Empty Values
<p>I have a stored procedure that performs a select statement in order to return user data to the session state on login.  So far, the stored procedure has only returned empty values when called by clicking the login button.  I have tested the stored procedure in Sql Mgmt Studio by running it after hard-coding an input username and achieved the desired results.  I have placed breakpoints in Visual Studio and found that while the username is being sent correctly from the textbox in which is was input, all parameters are being returned empty as "".  This leads me to believe the problem is something with formatting the input parameter, but I can't figure out what it is.  Any help is appreciated.</p>

<p>Here is my c# method:</p>

<pre><code>public static User GetUser(int? id, string username)
{
 string SelectById = "spSelectUserById";
 string SelectByUsername = "spSelectUserByUsername";
 User u = new User();            
 string connstring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
 using (SqlConnection conn = new SqlConnection(connstring))
 {
  SqlCommand cmd = new SqlCommand();
  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  if (string.IsNullOrEmpty(id.ToString()))
  {
   cmd = new SqlCommand(SelectByUsername, conn);
   cmd.Parameters.Add("@UserNameIn", SqlDbType.VarChar).Value = username.ToString();
  }
  else if (string.IsNullOrEmpty(username))
  {
   cmd = new SqlCommand(SelectById, conn);
   cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = id;
  }
   SqlParameter UserName = new SqlParameter();
   SqlParameter Password = new SqlParameter();
   SqlParameter FirstName = new SqlParameter();
   SqlParameter MiddleInitial = new SqlParameter();
   SqlParameter LastName = new SqlParameter();
   SqlParameter UserStar = new SqlParameter();
   SqlParameter UserRank = new SqlParameter();
   SqlParameter UserRankCode = new SqlParameter();
   SqlParameter Assignment = new SqlParameter();
   SqlParameter Shift = new SqlParameter();
   SqlParameter ContactPhone = new SqlParameter();
   SqlParameter PhoneType = new SqlParameter();
   SqlParameter Email = new SqlParameter();
   SqlParameter AssignmentId = new SqlParameter();
   SqlParameter ShiftId = new SqlParameter();
   UserName.ParameterName = "@UserName";
   Password.ParameterName = "@Password";
   FirstName.ParameterName = "@FirstName";
   MiddleInitial.ParameterName = "@MiddleInitial";
   LastName.ParameterName = "@LastName";
   UserStar.ParameterName = "@UserStar";
   UserRank.ParameterName = "@UserRank";
   UserRankCode.ParameterName = "@UserRankCode";
   Assignment.ParameterName = "@Assignment";
   Shift.ParameterName = "@Shift";
   ContactPhone.ParameterName = "@ContactPhone";
   PhoneType.ParameterName = "@PhoneType";
   Email.ParameterName = "@Email";
   AssignmentId.ParameterName = "AssignmentId";
   ShiftId.ParameterName = "ShiftId";
   UserName.SqlDbType = System.Data.SqlDbType.VarChar;
   Password.SqlDbType = System.Data.SqlDbType.VarChar;
   FirstName.SqlDbType = System.Data.SqlDbType.VarChar;
   MiddleInitial.SqlDbType = System.Data.SqlDbType.VarChar;
   LastName.SqlDbType = System.Data.SqlDbType.VarChar;
   UserStar.SqlDbType = System.Data.SqlDbType.Int;
   UserRank.SqlDbType = System.Data.SqlDbType.VarChar;
   UserRankCode.SqlDbType = System.Data.SqlDbType.VarChar;
   Assignment.SqlDbType = System.Data.SqlDbType.VarChar;
   Shift.SqlDbType = System.Data.SqlDbType.VarChar;
   ContactPhone.SqlDbType = System.Data.SqlDbType.VarChar;
   PhoneType.SqlDbType = System.Data.SqlDbType.VarChar;
   Email.SqlDbType = System.Data.SqlDbType.VarChar;
   AssignmentId.SqlDbType = System.Data.SqlDbType.Int;
   ShiftId.SqlDbType = System.Data.SqlDbType.Int;
   UserName.Size = 200;
   Password.Size = 200;
   FirstName.Size = 200;
   MiddleInitial.Size = 200;
   LastName.Size = 200;
   UserStar.Size = 200;
   UserRank.Size = 200;
   UserRankCode.Size = 200;
   UserRankId.Size = 200;
   Assignment.Size = 200;
   Shift.Size = 200;
   ContactPhone.Size = 200;
   PhoneType.Size = 200;
   Email.Size = 200;
   AssignmentId.Size = 200;
   ShiftId.Size = 200;
   UserName.Direction = System.Data.ParameterDirection.Output;
   Password.Direction = System.Data.ParameterDirection.Output;
   FirstName.Direction = System.Data.ParameterDirection.Output;
   MiddleInitial.Direction = System.Data.ParameterDirection.Output;
   LastName.Direction = System.Data.ParameterDirection.Output;
   UserStar.Direction = System.Data.ParameterDirection.Output;
   UserRank.Direction = System.Data.ParameterDirection.Output;
   UserRankCode.Direction = System.Data.ParameterDirection.Output;
   Assignment.Direction = System.Data.ParameterDirection.Output;
   Shift.Direction = System.Data.ParameterDirection.Output;
   ContactPhone.Direction = System.Data.ParameterDirection.Output;
   PhoneType.Direction = System.Data.ParameterDirection.Output;
   Email.Direction = System.Data.ParameterDirection.Output;
   AssignmentId.Direction = System.Data.ParameterDirection.Output;
   ShiftId.Direction = System.Data.ParameterDirection.Output;
   cmd.Parameters.Add(UserName);
   cmd.Parameters.Add(Password);
   cmd.Parameters.Add(FirstName);
   cmd.Parameters.Add(MiddleInitial);
   cmd.Parameters.Add(LastName);
   cmd.Parameters.Add(UserStar);
   cmd.Parameters.Add(UserRank);
   cmd.Parameters.Add(UserRankCode);
   cmd.Parameters.Add(Assignment);
   cmd.Parameters.Add(Shift);
   cmd.Parameters.Add(ContactPhone);
   cmd.Parameters.Add(PhoneType);
   cmd.Parameters.Add(Email);
   cmd.Parameters.Add(AssignmentId);
   cmd.Parameters.Add(ShiftId);
   conn.Open();
   cmd.ExecuteNonQuery();
   u.UserName = UserName.Value.ToString();
   u.Password = Password.Value.ToString();
   u.FirstName = FirstName.Value.ToString();
   u.MiddleInitial = MiddleInitial.Value.ToString();
   u.LastName = LastName.Value.ToString();
   u.UserStar = Convert.ToInt16(UserStar.Value);
   u.UserRank = UserRank.Value.ToString();
   u.UserRankCode = UserRankCode.Value.ToString();
   u.Assignment = Assignment.Value.ToString();
   u.AssignmentId = Convert.ToInt16(AssignmentId.Value);
   u.Shift = Shift.Value.ToString();
   u.ShiftId = Convert.ToInt16(ShiftId.Value.ToString());
   u.ContactPhone = ContactPhone.Value.ToString();
   u.PhoneType = PhoneType.Value.ToString();
   u.Email = Email.Value.ToString();
   return u;
  }
 }
</code></pre>

<p>Here is my stored procedure:</p>

<pre><code>CREATE PROCEDURE spSelectUserByUsername
@UserNameIn         VARCHAR(MAX)    =   NULL                ,
@UserId             INT             =   NULL        OUTPUT  ,
@FirstName          VARCHAR(MAX)    =   NULL        OUTPUT  ,
@MiddleInitial      VARCHAR(MAX)    =   NULL        OUTPUT  ,
@LastName           VARCHAR(MAX)    =   NULL        OUTPUT  ,
@UserStar           INT             =   NULL        OUTPUT  ,
@UserRank           VARCHAR(MAX)    =   NULL        OUTPUT  ,
@UserRankCode       VARCHAR(MAX)    =   NULL        OUTPUT  ,
@UserRankId         INT             =   NULL        OUTPUT  ,
@Assignment         VARCHAR(MAX)    =   NULL        OUTPUT  ,
@AssignmentId       INT             =   NULL        OUTPUT  ,
@Shift              VARCHAR(MAX)    =   NULL        OUTPUT  ,
@ShiftId            INT             =   NULL        OUTPUT  ,   
@ContactPhone       VARCHAR(MAX)    =   NULL        OUTPUT  ,
@PhoneType          VARCHAR(MAX)    =   NULL        OUTPUT  ,
@Email              VARCHAR(MAX)    =   NULL        OUTPUT
AS
BEGIN

SELECT 
 @UserId            =   users.user_id                               ,
 @FirstName     =   users.first_name                            ,
 @LastName      =   users.last_name                             ,
 @MiddleInitial =   users.middle_initial                        ,
 @UserStar      =   users.user_star                             , 
 @UserRank      =   CONCAT(users.user_rank,' - ',ranks.title)   ,
 @UserRankCode  =   users.user_rank                             ,
 @UserRankId        =   ranks.id                                    ,
 @AssignmentId  =   users.assignment                            ,
 @Assignment        =   assignment.AssignmentName                   ,
 @ShiftId       =   users.regular_shift                         ,
 @Shift         =   s.DisplayName                               ,
 @ContactPhone  =   users.contact_phone                         ,
 @PhoneType     =   users.phone_type                            ,
 @Email         =   users.email                                     
FROM 
dbo.users
left JOIN
[shift] s   ON  users.regular_shift     =   s.shiftid
left JOIN
assignment  ON  users.assignment        =   assignment.AssignmentId
left JOIN
ranks       ON  users.user_rank         =   ranks.code
WHERE
users.username                          =   @UserNameIn
END
</code></pre>