Stored Procedure to validate User Login and return the User Entity From SQL Server

-- ============================================= 

-- Description: Validate User Login and return the User Entity 

-- ============================================= 

CREATE PROCEDURE [dbo].[SP_User_Login]  

 @LoginID AS VARCHAR(20), 

 @Password AS VARCHAR(20) 

AS 

BEGIN 

 

 

 DECLARE @UserID AS INT 

 DECLARE @LastLogon AS DATETIME 

 

 SELECT @UserID = UserID, @LastLogon = LastLogon  

 FROM [User] 

 WHERE LOWER(LoginID) = LOWER(@LoginID) 

  AND Password = @Password AND IsActive = 1 

 

 IF @UserID IS NOT NULL 

 BEGIN 

  

  UPDATE [User] 

  SET LastLogon = GETDATE() 

  WHERE UserID = @UserID 

 END 

  

 SELECT UserID, UserType, FirstName, LastName, LoginID, Designation,  

 PhoneOffice, PhoneResidence, PhoneMobile, PhoneOther, Email,  

 Title, @LastLogon AS LastLogon, IsActive 

 FROM [User] 

 WHERE UserID = @UserID 

 

END