CODEWITHSUNDEEP
sqlsql-server
C.Das
C.Das

Reputation: 127

Return user information after validate user in stored procedure?

I have a stored procedure to validate a user. After validate right now I am returning true if user is validated but how can I return all the details of that user instead of true?

IF (@Flag='Authenticate')
BEGIN
IF EXISTS(SELECT UserID  FROM UserInformation WITH(NOLOCK)WHERE UserName=@UserName and Password=@UserPassword )
 BEGIN
  INSERT INTO UserLoginHistory(UserId,LastLoggedIn)
  SELECT @Userid,GETDATE()
  select 'true' as [Output] 
 END
END

Upvotes: 0

Views: 335

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You don't need a separate "if" to check if the user already exists. You can put that all into a single query:

IF (@Flag = 'Authenticate')
BEGIN
    INSERT INTO UserLoginHistory(UserId, LastLoggedIn)
        SELECT v.Userid, GETDATE()
        FROM (VALUES (@UserId)) v(UserId)
        WHERE EXISTS (SELECT 1
                      FROM UserInformation ui
                      WHERE ui.UserName = @UserName AND ui.Password = @UserPassword
                     );

     SELECT ui.*
     FROM UserInformation ui
     WHERE ui.UserName = @UserName AND ui.Password = @UserPassword;
END;

Also, I am concerned about @UserPassword. Hopefully that value is encrypted! You should not have clear-text passwords anywhere in an application that has a user other than you -- even for a demo or course.

Upvotes: 0

Mukesh Arora
Mukesh Arora

Reputation: 1813

Try something like below query - You can declare more variables as needed, and store all those information in variables which you want to return.

IF (@Flag='Authenticate')
BEGIN

Declare @UserID varchar(50) = null

SELECT @UserID = UserID  FROM UserInformation WITH(NOLOCK) WHERE UserName=@UserName and Password=@UserPassword )
 IF (@UserID is not NULL)
 BEGIN

  INSERT INTO UserLoginHistory(UserId,LastLoggedIn)
  SELECT @Userid,GETDATE()

  SELECT @Userid 
 END
END

Upvotes: 1

Related Questions