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
– James Z, Commented Mar 12, 2016 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
– markabarmi, Commented Mar 12, 2016 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' — IVNSTN
– IVNSTN, Commented Mar 12, 2016 at 11:00

marc_s · Accepted Answer · 2016-03-12 11:18:23Z

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!

Collectives™ on Stack Overflow

How to check if a value exists in the database

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related