IF NOT EXIST insert ELSE IF update ELSE IF EXISTS and is not Numeric DELETE From WHERE

Question

I want to modify the Stored Procedure below.

@UserID INT
@Unlock VARCHAR(4)

AS
SET NOCOUNT ON

IF NOT EXISTS (SELECT * FROM dbo.tblUnlockCode WHERE iUserID = @UserID)
BEGIN
IF ISNUMERIC(@Unlock) = 1
BEGIN 
INSERT dbo.tblUnlockCode (iUserID, sUnlockCode) VALUES (@UserID, @Unlock)
END

I would actually like to add to it, to where if the iUserID exists Update the @Unlock to the new Pin, and if the iUserID exists on the table but the @Unlock gets erased on the textfield(in access) it gets removed from the Table. I only want to store the iUserIDs that 4 digit pins. How could I do that?

P.S. on access I am checking if the pin is 4 digits.

Not related, but, for one example, this four "digit" PIN `SELECT ISNUMERIC('$0.0')` returns `1`. Maybe have a look here: https://stackoverflow.com/questions/312054/efficient-isnumeric-replacements-on-sql-server — Eric Brandt, Apr 19 '19 at 17:33

Vasya · Accepted Answer · 2019-04-19T21:17:12.350

0

Try this (I also recommend adding error handling if you have none):


    @UserID INT
    @Unlock VARCHAR(4)

    AS
    SET NOCOUNT ON

    IF NOT EXISTS (SELECT * FROM dbo.tblUnlockCode WHERE iUserID = @UserID)
    BEGIN
      IF ISNUMERIC(@Unlock) = 1
      BEGIN 
        INSERT dbo.tblUnlockCode (iUserID, sUnlockCode) VALUES (@UserID, @Unlock)
      END   
    END
    ELSE
    BEGIN
      IF ISNUMERIC(@Unlock) = 1
      BEGIN 
        UPDATE dbo.tblUnlockCode set sUnlockCode=@Unlock WHERE iUserID= @UserID
      END   
      ELSE --Assuming you want to clear the record if unlock code is empty
      BEGIN
        DELETE dbo.tblUnlockCode WHERE iUserID= @UserID      
      END
    END

edited Apr 19 '19 at 21:17

answered Apr 19 '19 at 17:35

Vasya

469
2
6

Sounds reasonable to me, however wouldn't DELETE dbo.tblUnlockCode erase the whole table? It's a little nerve wrecking using anything to do with Delete. – Cookies Apr 19 '19 at 19:24
@Cookies: delete statement has ```WHERE iUserID= @UserID```. It will delete all records for a particular UserID but not all records. If that's not what you want, you can change it. – Vasya Apr 19 '19 at 19:38
Okay no it sounds like it does what i want it to do. Thanks ! – Cookies Apr 19 '19 at 19:46
I am having some trouble with the BEGINs and ENDs. I keep getting an error. I added a third END in the end but now it doesn't seem to read the send else statement. I update a user and it does not update in the table. – Cookies Apr 19 '19 at 21:14
@Cookies: "END" was missing before the first "ELSE". I fixed it. – Vasya Apr 19 '19 at 21:17
That did it! Thanks for your help. – Cookies Apr 19 '19 at 21:34

IF NOT EXIST insert ELSE IF update ELSE IF EXISTS and is not Numeric DELETE From WHERE

1 Answers1