locked
Validate user if retired Checkbox is checked RRS feed

  • Question

  • User-1901014284 posted

    Hi,

    I have the below Validation code for a user login:

    USE [CRM]
    GO
    /****** Object:  StoredProcedure [dbo].[Validate_User]    Script Date: 01/07/2019 13:27:42 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[Validate_User]
          @Username varchar(200),
          @Password varchar(50)
    AS
    BEGIN
          SET NOCOUNT ON;
          DECLARE @ID INT, @LastLoginDate DATETIME
         
          SELECT @ID = ID, @LastLoginDate = @LastLoginDate
          FROM LoginDetails WHERE Username = @Username AND [Password] = @Password
         
          IF @ID IS NOT NULL
          BEGIN
                IF NOT EXISTS(SELECT ID FROM LoginDetails WHERE ID = @ID)
                BEGIN
                      UPDATE LoginDetails
                      SET LastLoginDate = GETDATE()
                      WHERE ID = @ID
                      SELECT @ID [ID] -- User Valid
                END
                ELSE
                BEGIN
                      SELECT -2 -- User not activated.
                END
          END
          ELSE
          BEGIN
                SELECT -1 -- User invalid.
          END
    END

    I have a retired checkbox which allows admins to select if a user has been retired/left the company. The retired checkbox value is saved as a bit value in the backend database. I would like the above validation to check if the value of the users retired equals 1 to deny access to the system. I am unsure how I can get this to run.

    Any help would be greatly appreciated.

    Many thanks in advance.

    Jonny

    Monday, July 1, 2019 12:39 PM

Answers

  • User-821857111 posted
    SELECT @ID = ID, @LastLoginDate = @LastLoginDate
          FROM LoginDetails WHERE Username = @Username AND [Password] = @Password AND Retired = 0
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 1, 2019 12:47 PM
  • User475983607 posted

    Your code and question are a bit confusing.  Your question about a checkbox but the you are showing TSQL code.  The TSQL code cannot possible function as expected as it does an update if the if the ID is not found in the LoginDetials table. Lastly, there is no input for the checkbox in the procedure.  But why you need the in the first place?.  Shouldn't you simply add to the where clause?

    SELECT @ID = ID, @LastLoginDate = @LastLoginDate
    FROM LoginDetails 
    WHERE Username = @Username 
    	AND [Password] = @Password 
    	AND [Retired] = 0

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 1, 2019 12:56 PM

All replies

  • User-821857111 posted
    SELECT @ID = ID, @LastLoginDate = @LastLoginDate
          FROM LoginDetails WHERE Username = @Username AND [Password] = @Password AND Retired = 0
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 1, 2019 12:47 PM
  • User475983607 posted

    Your code and question are a bit confusing.  Your question about a checkbox but the you are showing TSQL code.  The TSQL code cannot possible function as expected as it does an update if the if the ID is not found in the LoginDetials table. Lastly, there is no input for the checkbox in the procedure.  But why you need the in the first place?.  Shouldn't you simply add to the where clause?

    SELECT @ID = ID, @LastLoginDate = @LastLoginDate
    FROM LoginDetails 
    WHERE Username = @Username 
    	AND [Password] = @Password 
    	AND [Retired] = 0

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 1, 2019 12:56 PM
  • User-1901014284 posted

    Thank you both very much, this has helped resolve my issue. Apologies if my question was confusing.

    Kind regards

    Jonny

    Tuesday, July 2, 2019 12:31 PM