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.

• good-to-know: Is spacing the thing I can improve myself? WooHoo then! :-)
• PenutReaper: @WP_Geek You should be able to alter the formatting, you are able to alter/create SP's right? Anyways, I'd liken this query to a freshly baked caked. It's delicious and well made, but it needs a little fondant (formatting) to finish it up.
• aydjay: You might want to check out: architectshack.com/PoorMansTSqlFormatter.ashx You can put it in ssms and it will do it for you.