3
\$\begingroup\$

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?

\$\endgroup\$
2
\$\begingroup\$

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 
\$\endgroup\$
0
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Is spacing the thing I can improve myself? WooHoo then! :-) \$\endgroup\$ – good-to-know Apr 27 '15 at 13:58
  • \$\begingroup\$ @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. \$\endgroup\$ – PenutReaper Apr 27 '15 at 14:31
  • \$\begingroup\$ You might want to check out: architectshack.com/PoorMansTSqlFormatter.ashx You can put it in ssms and it will do it for you. \$\endgroup\$ – d347hm4n Apr 28 '15 at 10:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.