CREATE PROCEDURE usp_ValidateLogin 
  @inLogin varchar(10),
  @inPassword varchar(10)
 AS
   Declare @tempCount  int
   Declare @outLoginStatus char(2)
   Declare @outEmpId int
   Declare @outRoleId smallint

BEGIN
   set @inLogin = UPPER(RTRIM(LTRIM(@inLogin)))
   set @inPassword = RTRIM(LTRIM(@inPassword))
   set @inPassword = CAST(@inPassword AS varbinary(10)) -- trick for case sensitive comparision

   set @outLoginStatus = 'WL'  -- Default - Wrong Login
   set @outEmpId = 0 -- Default ' Employee doesn't exist
   set @outRoleId = 0 --Default

   Select @tempCount = count(EmpId) from Employees where (UPPER(ELogin)=@inLogin)
   if @tempCount = 0 
      Begin
          set @outLoginStatus = 'WL'
          set @outEmpId = 0 -- Default
          set @outRoleId = 0 --Default
          select @outLoginStatus, @outEmpId, @outRoleId
      End
   Else
      Begin
           select @tempCount = count(EmpId) from Employees Where((UPPER(ELogin)=@inLogin) and (CAST(EPassword as varbinary(10))=@inPassword)) ---> THIS IS NOT WORKING
           If (@tempCount = 0) 
                Begin
                    set @outLoginStatus = 'WP' 
                    select @outLoginStatus, @outEmpId, @outRoleId
                End
            Else
                Begin
                   set @outLoginStatus = 'LP'
                   select @outEmpId = EmpId, @outRoleId = RoleId from Employees where ((UPPER(ELogin)=@inLogin) and (EPassword=@inPassword)) 
                    select @outLoginStatus, @outEmpId, @outRoleId
                 End
      End
END
GO
**************************************************************