March 12, 2016 at 3:36 am
Hi all
First time post on here so bare with me :).
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 http://stackoverflow.com/questions/1175217/sql-server-if-not-exists-usage
Please can someone help me
Many thanks Mark
March 12, 2016 at 6:16 am
Can you please post the DDL (create script for the tables), sample data as insert script and the expected results?
😎
March 12, 2016 at 7:55 am
Since this is your first time posting here, welcome to SSC.
I think your logic could be simplified a bit by using this:
IF (SELECT COUNT(*) FROM dbo.Clock WHERE ClockIn = '12/03/2015' AND Username = 'ROSE') = 0
BEGIN
INSERT INTO dbo.Clock (clockIn, UserName)
VALUES(GetDate(), 'Rose');
END;
ELSE
BEGIN
RAISERROR('CLOCK IS TRUE NOTHING MORE TO DO', 16 ,1);
ROLLBACK TRANSACTION;
END;
Erikur's request would answer the real question I see - What's the data type of ClockIn? If it's a datetime and you're comparing the value to a date, then you're going to be inserting a new row every time. The datetime data type includes the time with a precision of 3.3 ms, so it only equals a date at one time per day - midnight.
March 12, 2016 at 8:01 am
.....me thinks you maybe comparing a "date" with a "datetime"....(what datatype is "clockin"?) :::: edit...see Ed has already mentioned this
maybe
IF (SELECT COUNT(*) FROM dbo.Clock WHERE convert(date,ClockIn) = convert(date,getdate()) AND Username = 'ROSE') = 0
BEGIN
INSERT INTO dbo.Clock (clockIn, UserName)
VALUES(GetDate(), 'Rose');
END;
ELSE
BEGIN
RAISERROR('CLOCK IS TRUE NOTHING MORE TO DO', 16 ,1);
ROLLBACK TRANSACTION;
END;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 13, 2016 at 10:10 pm
As for me, there are 2 many words for such a simple operation.
This should do:
INSERT INTO [Clock] ([clockIn], [UserName])
VALUES(GetDate(), 'Rose')
WHERE NOT EXISTS (
SELECT * FROM [Clock] WHERE [clockIn] ='12/03/2016'
AND UserName = 'ROSE'
)
If you really need to raise some error (why would you need it on a non-front end layer?) then add this:
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('CLOCK IS TRUE NOTHING MORE TO DO',16 ,1)
ROLLBACK TRAN
END
Hardcoded dae does not look right to me.
If you are trying to check if there was a record already added today then you can code it like this:
WHERE [clockIn] >=DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply