Comparing a User Input to an already established table's column

Question

I have a User Input coming into the database to get compared to a table's column. I've finding a ton of information on a column comparing another column, which didn't seem to work correctly.

Here's my code:

CREATE Procedure Check_Previous_Passwords
    @ua_pk uniqueidentifier,
    @IncomingPassword varchar(25)
AS
    DECLARE @TempTable TABLE (Passwords varchar(25))

    INSERT INTO @TempTable
    SELECT *
    FROM User_Passwords
    WHERE ua_fk = @ua_pk

    IF @IncomingPassword = @TempTable.Passwords
        --Then do stuff

GO

I'm pretty sure it's something I'm completely overlooking. Thanks!

Answer 1

CREATE Procedure Check_Previous_Passwords
    @ua_pk uniqueidentifier,
    @IncomingPassword varchar(25)
AS
    DECLARE @Temp VARCHAR(25)

    SET @Temp = (SELECT TOP 1 Password 
                 FROM User_Passwords 
                 WHERE ua_fk = @ua_pk 
                 ORDER BY someDate DESC)


    IF @IncomingPassword = @Temp
        BEGIN
              SELECT 'You can't reuse the same PW'
        END
    ELSE
        BEGIN
              --do work
        END

GO

This just checks the last password to make sure that it's not the same. If you want to check the last N number of passwords we can use the IN clause or EXISTS

CREATE Procedure Check_Previous_Passwords
    @ua_pk uniqueidentifier,
    @IncomingPassword varchar(25)
AS
    DECLARE @Temp VARCHAR(25)

    SET @Temp = (SELECT TOP 1 Password 
                 FROM User_Passwords 
                 WHERE ua_fk = @ua_pk 
                 ORDER BY someDate DESC)


    IF (EXISTS (SELECT 1 FROM User_Passwords up where up.ua_fk = @ua_pk and @IncomingPassword = up.Password))
        BEGIN
              SELECT 'You can't reuse the same PW'
        END
    ELSE
        BEGIN
              --do work... like an insert
        END

GO