How to check if a value exists in the database

Question

I have the following code which I am trying to use to check if a value is already stored inside the db.

IF EXISTS
(
    SELECT * FROM [Clock] WHERE [clockIn] ='12/03/2016'
             AND UserName = 'ROSE'
)
BEGIN 
    RAISERROR('CLOCK IS TRUE NOTHING MORE TO DO',16 ,1)
    ROLLBACK TRAN
END
ELSE
IF NOT EXISTS
(
    SELECT * FROM [Clock] WHERE [clockIn] ='12/03/2016'
    AND UserName = 'ROSE'
)
    BEGIN
        INSERT INTO [Clock] ([clockIn], [UserName])
        VALUES(GetDate(), 'Rose')
    END

I'm not entirely sure why this is working as it always seems to insert a new row into the db and I am puzzled. I have used the following as an example SQL Server IF NOT EXISTS Usage? and also SQL Server 2008 - IF NOT EXISTS INSERT ELSE UPDATE as reference.

Is your ClockIn data type date? If it's datetime then you'll have also hours, minutes etc. and your comparison won't match. Using country specific date formats is a bad idea, use YYYYMMDD instead. — James Z, Mar 12 '16 at 10:51
Thanks for the reply @JamesZ. My fields are of Datatype DATE. If I convert this to datatype of datetimw will this help to resolve my issue? — markabarmi, Mar 12 '16 at 10:56
You are adding rows with `getdate` value but trying to locate them with constant date filter. Also your date format is not locale-safe. Try '20160312' — Ivan Starostin, Mar 12 '16 at 11:00

marc_s · Accepted Answer · 2016-03-12T11:18:23.240

Code seems ok to me - but the IF NOT EXISTS in the ELSE case is superfluous - you already checked and got back the fact that that value does not exist - so there's really no need to check again.

However: you're checking for existence of a row with a fixed date specified in a string literal - that'll work today, but not anymore tomorrow. I'd recommend to first get the current date into a variable, and then use that variable both for the checking and the possible insert.

-- store today's date in a variable  
DECLARE @Today DATE = SYSDATETIME();

-- check if that row already exists in table
IF EXISTS (SELECT * FROM [Clock] 
           WHERE [clockIn] = @Today AND UserName = 'ROSE')
BEGIN 
    RAISERROR('CLOCK IS TRUE NOTHING MORE TO DO',16 ,1)
    ROLLBACK TRAN
END
ELSE
BEGIN
    INSERT INTO [Clock] ([clockIn], [UserName])
    VALUES(@Today, 'Rose')
END

As a side note: I'd recommend to use the ISO-8601 format when specifying dates as string literals - YYYYMMDD. That format will work with any language and/or regional settings in your (and anybody else's) SQL Server, while any other format can be dependent on the settings and might not work all the time!

How to check if a value exists in the database

1 Answers1