Stored proceudre for login page

CREATE TABLE Usertable(
	[Pk_ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[UserID] [varchar](10) NOT NULL,
	[UserName] [varchar](50) NOT NULL,
	[Password] [binary](16) NOT NULL,
	[IncorrectLoginAttempts] [smallint] NOT NULL,--incorrect login attempts
	[LastIncorrectLoginAttemptsDate] [datetime] NOT NULL,-- last incorrect login attempt ate
	[LoginLockStat] [char](1) NOT NULL,-- user login locked or not
	[StatFlag] [char](1) NOT NULL,	-- to indicate user live or not
	[CrtdDt] [datetime] NOT NULL,
	[CrtdBy] [varchar](10) NOT NULL,
	[LstModDt] [datetime] NOT NULL,
	[LastPasswordChangeDate] [datetime] NOT NULL,
	[LstModBy] [varchar](10) NOT NULL,)

--Add primary to [UserID] column

--insert data into table

--login button click call store procedure here password store in the table in binary formate which is actually encrypted text.while checking for psw match send encrypted password to your login store procedure.

-- your login store procedure

if OBJECT_ID('SPLogin') is not null
	drop procedure SPLogin
Go 
    
CREATE procedure SPLogin    
(    
	
	@UserId					varchar(10),    
	@Password					binary(16),    
	@SocietyName			varchar(10),    
	@AccPeriodDesc			varchar(10)    
)    
as    
Begin    
 Declare @LoginLockStat as  char  
 Declare @InvalLoginAttmpt as int 
 
 set @InvalLoginAttmpt=(select IncorrectLoginAttempts from Usertable where UserID=@UserId) 
  
  -- validate user access
  
  if ((select LoginLockStat from Usertable where UserID=@UserId)='Y') 
  Begin
	  Raiserror('User access is Locked',16,1) 
	  Return -1 
  End
  
  if @InvalLoginAttmpt =3
  Begin
	  begin tran
			update Usertable 
			set IncorrectLoginAttempts =@InvalLoginAttmpt,
			LoginLockStat ='Y',
			LastIncorrectLoginAttemptsDate =GETDATE()
			where UserID=@UserId 
	  
	  If @@RowCount=0 OR @@Error <>0 
			Begin
				RaisError('Error while Updating data',16,1)
				rollback 
				Return -1 
			End 
	  commit
	  Raiserror('User access is Locked',16,1) 
	  Return -1 
  End
  
  
  -- validate Password expire

  -- to check whether password expire or not.this is optional whether you want force to user change password after every month
  
  if DATEDIFF (dd,convert(date,(select LastPasswordChangeDate  from Usertable s  where s.UserId like Upper(@UserId)),103),Getdate() )>=30
  Begin  
	Raiserror('Password Expired',16,1)
	Return -1
  End

  select * from SA_User_h
  -- =======================================================================================
	-- Query to Validate User
	-- =======================================================================================
  
	
	
		
		if exists(select null from Usertable 
				  where UserID=@UserId 
				  And Password=@Password
				  And StatFlag='L')
		Begin
			begin tran
			update Usertable 
			set IncorrectLoginAttempts =0
			where UserID=@UserId  
			
			If @@RowCount=0 OR @@Error <>0 
			Begin
				RaisError('Error while Updating data',16,1)
				rollback 
				Return -1 
			End 
			Commit
			select 'success ful login'
		End
		Else
		Begin  
			begin tran
			update Usertable 
			set IncorrectLoginAttempts =@InvalLoginAttmpt+1,
			LoginLockStat =(case when @InvalLoginAttmpt+1=3 then 'Y' else 'N' End ),
			LastIncorrectLoginAttemptsDate =GETDATE()
			where UserID=@UserId  
			
			If @@RowCount=0 OR @@Error <>0 
			Begin
				RaisError('Error while Updating data',16,1)
				rollback 
				Return -1 
			End 
			Commit
			
			if ((select LoginLockStat from Usertable where UserID=@UserId)='Y')
			Begin
				Raiserror('User access is Locked',16,1) 
				Return -1 
			End
			Else
			Begin
				Raiserror('Invalid Login or Password. Try Again',16,1)  
				Return -1  
			End	
		End  				  

End