title: Return an integer value from sql stored procedure
body: <p>I have a stored procedure:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[TG_CheckUserLoginStatus]
      -- Add the parameters for the stored procedure here
      (
          @UserName nvarchar(100),
          @UserStatus INT output
      )
  
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
      SET NOCOUNT ON;
  
      -- Does the user exist
      If  NOT (EXISTS (SELECT name FROM sys.sql_logins WHERE (name = @UserName)))
          SELECT @UserStatus = 0;
  
      ELSE    If EXISTS (SELECT name FROM sys.sql_logins WHERE ((name = @UserName) AND 
                      ((LOGINPROPERTY(name, 'IsExpired')  = 1) OR ((LOGINPROPERTY(name, 'IsMustChange') = 1)))))
                  SELECT @UserStatus = 1;
              ELSE
                  SELECT @UserStatus = 2;
  
  
  END
  </code></pre>
  
  <p>The problem is that it does not return the correct integer value</p>
  
  <p>Here's the calling routine (VB)</p>
  
  <pre><code>Using oCmd As New SqlCommand("TG_CheckUserLoginStatus")
              With oCmd
                  .CommandType = CommandType.StoredProcedure
                  .Connection = mTGcon.DbConnection
                  .Parameters.AddWithValue("@UserName", txtUserName.Text) 'Sends the prospective user name to the SP
              End With
              'We are getting back an integer value
              ' 0 = Username is wrong , 1= Must change Password, 2= all good
              'Create the return parameter
              Dim op As New SqlParameter("@UserStatus", SqlDbType.Int)
              op.Direction = ParameterDirection.Output
              'Add this to the cmd
              oCmd.Parameters.Add(op)
  
              With mTGcon
                  .OpenDb()
                  If Not .IsOpen Then
                      MessageBox.Show("Unable to access database", "form will close", MessageBoxButtons.OK, MessageBoxIcon.Stop)
                  Else
                      ' see if the username is valid and if so requires a password change
                      mRetVal = CType(oCmd.ExecuteScalar, Integer)
  
                  End If
              End With
          End Using
  </code></pre>
  
  <p>The value received does not agree  with the value I get when I execute it with sql management studio.</p>
  
  <p>What am I missing here ?</p>