stored procedure for login

select @INtegerVariable = count(UserName) from Login where UserName=@uname and  Password=@pass

Select @INtegerVariable

select * from UserMstr where Password=@Password and Password in (select password from UserMstr where UserName=@Username and IsDeleted='False' and IsActive='True')    and UserName=@Username      

create or replace procedure sp_login
(
p_user_name Login.UserName%type,
p_password  Login.Password%type,
p_message out varchar2
)is

v_user_count number(1):=0

begin

   if p_username='' or p_password='' then
      p_message='Username Or Password Cannot Be Blank';
   end if;

   begin
          select count(*) into v_user_count from <login> where Username=p_user_name and   Password=p_password;        
      exception
            when others then
              raise_application_error('-20001','Error While Retrieving Data.');
   end;

   if v_user_count=0 then
         p_message:='Invalid Login Details';
   elsif v_user_count=1 then
         p_message:='Success';
   else
         p_message:='Critical Error.';
   end if;

end;

CREATE PROCEDURE login
(
	@username varchar(100)='',
	@password varchar(100)=''
)
As
Begin
	if exists(select 1 from Login where where UserName=@username and  Password=@password) 
	Begin
		Select UserName,Password,Designation from Login where UserName=@username and  Password=@password 
	End
End