Updating user log in time while he logs in to the web page

Question

My intention is to store the user's last login date and time when he logs in to the web page. I pass his username and password to the stored procedure to check whether is it correct or not! I believe it'll be fine to store his last login date and time if any record matches.

USE [DBNAME]
GO
/****** Object:  StoredProcedure [dbo].[WP_LogInUser]    Script Date: 27-Apr-15 4:09:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[WP_LogInUser] 
    @Username   Nvarchar(100),
    @Password   Nvarchar(100)

as
Begin 
    if exists(Select Username from TableName where Username=@Username and UserPassword=@Password)
        Begin
            Update TableName Set UserLastLogin = GETUTCDATE() where Username=@Username and UserPassword=@Password and IsActive = 1
            Select UserID, Username, UserEmail, UserFullName, UserType from TableName where Username=@Username and UserPassword=@Password and IsActive = 1
        End
End

What do you think about it? Is it okay? Or can I make this even better?

Answer 1

The first point I notice looking at your code is that it looks as though you are storing passwords in plain text. You should not do this. It is highly irresponsible. They should be salted and hashed in almost all cases.

Secondly there is no point looking up the same information three times.

Once to check if it exists, secondly to update it, and thirdly to select it.

You can do all this in one operation.

ALTER PROCEDURE [dbo].[WP_LogInUser] @Username NVARCHAR(100),
                                     @Password NVARCHAR(100)
AS
  BEGIN
      UPDATE TableName
      SET    UserLastLogin = GETUTCDATE()
      OUTPUT INSERTED.UserID,
             INSERTED.Username,
             INSERTED.UserEmail,
             INSERTED.UserFullName,
             INSERTED.UserType
      FROM   TableName
      WHERE  Username = @Username
             AND UserPassword = @Password
             AND IsActive = 1
  END 

Answer 2

Spacing, The Final Frontier

I can't see anything that majorly needs changing with this SP. The only things I would suggest, is being a little more liberal with your enter key and more consistent with either capitalising or lower-casing keywords.

I find this much easier to parse than the query you posted, even though I haven't changed anything functional:

USE [DBNAME]
GO

/* 
    Object:  StoredProcedure [dbo].[WP_LogInUser]    
    Script Date: 27-Apr-15 4:09:38 PM 
*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[WP_LogInUser] 
    @Username   NVARCHAR(100),
    @Password   NVARCHAR(100)
AS
BEGIN
    IF EXISTS
    (
        SELECT Username
        FROM TableName
        WHERE Username = @Username
        AND UserPassword = @Password
    )
    BEGIN

        UPDATE TableName
            SET UserLastLogin = GETUTCDATE() 
        WHERE Username = @Username
        AND UserPassword = @Password 
        AND IsActive = 1

        SELECT 
            UserID,
            Username,
            UserEmail,
            UserFullName,
            UserType
        FROM TableName
        WHERE Username = @Username 
        AND UserPassword = @Password 
        AND IsActive = 1

    END
END 

A little more spacing can really help anyone reading your code.