CODEWITHSUNDEEP
sql-serverstored-procedures
Kevin Fischer
Kevin Fischer

Reputation: 352

Stored Procedure that needs to delete from table, then compare incoming value

I'm kind of stuck on how I should proceed with my stored procedure. The idea of this SP is to take these incoming values, compare them to my User_Passwords table. See if that table is too long based off of company policy. Delete the old ones to shrink the table to the correct size. Then compare the incoming password with the previous passwords to throw an error on my VB.Net code stating that they need to choose a different password because it was used before. I've made a comment on where I'm stumped.

DECLARE @UserNumberOfPasswords INT
DECLARE @ua_pk uniqueidentifier = GUID
DECLARE @ResetDaysAmount INT = 30
DECLARE @AllowedNumberOfPasswords INT = 10
DECLARE @CurrentPasswordDate DATE = GetDate()
DECLARE @CurrentPassword varchar(25) = 'Password'
DECLARE @PreviousPassword BIT = 0

SELECT *
FROM User_Passwords
WHERE ua_fk = @ua_pk
ORDER BY up_PasswordDate ASC

SELECT @UserNumberOfPasswords = COUNT(*)    
FROM User_Passwords AS up
WHERE ua_fk = GUID

IF @UserNumberOfPasswords > @AllowedNumberOfPasswords
BEGIN
    WITH T
    AS (SELECT TOP (@UserNumberOfPasswords - (@AllowedNumberOfPasswords - 1)) *
        FROM   User_Passwords
        WHERE  ua_fk = @ua_pk
        ORDER BY up_PasswordDate ASC)
    DELETE FROM T; 
END

IF @UserNumberOfPasswords = @AllowedNumberOfPasswords
BEGIN
    WITH T
    AS (SELECT TOP 1 *
        FROM User_Passwords
        WHERE ua_fk = @ua_pk
        ORDER BY up_PasswordDate ASC)
    DELETE FROM T;
END

--Where I'm stumped.  I have tried to use 'up_Password' but it's throwing an error
--"the multi-part identifier "User_Passwords.up_Password" could not be bound".
--The column's type is varchar(25), just like @CurrentPassword

IF @CurrentPassword = User_Passwords.up_Password
BEGIN
    WITH T
    AS (SELECT *
        FROM User_Passwords
        WHERE ua_fk = @ua_pk
        ORDER BY up_PasswordDate ASC)
        --I know this isn't completed but I want to change the BadPassword =1

Later on in the SP, I'll be adding to the table, if the password is a new password.

Upvotes: 1

Views: 34

Answers (1)

David Rushton
David Rushton

Reputation: 5030

@SeanLange raises a very good point. I would recommend you follow this up.

IF @CurrentPassword = User_Passwords.up_Password is not a valid statement. Exists allows you to check if a query returns any results.

Here is an example:

IF EXISTS (SELECT 1 FROM User_Passwords WHERE up_Password = @CurrentPassword)
    BEGIN

        PRINT 'We got one'
    END
ELSE
    BEGIN

        PRINT 'No match'
    END

Upvotes: 3

Related Questions