80

I apologize, but this is kind of a two part question.

I'm extremely new to SQL and am trying to develop a time clock application for the small office that I work in. I'm playing around with the SQL backend right now and have a question about compound statements.

Where I'm stuck is if a user tries to clock out for break but never clocked in at the start of the shift, SQL needs to create a new row rather than update an existing.

Here is what I tried:

 IF NOT EXISTS(SELECT * FROM Clock WHERE clockDate = '08/10/2012') AND userName = 'test') BEGIN INSERT INTO Clock(clockDate, userName, breakOut) VALUES({ fn NOW() }, 'test', { fn NOW() }) END ELSE BEGIN UPDATE Clock SET breakOut = { fn NOW() } WHERE (clockDate = '08/10/2012') AND (userName = 'test') END

I'm using Visual Studio 2010 to do this connected to SQL Server Express 2008 on my local machine. I get an error that says "The Compound statement SQL construct or statement is not supported." However, that is followed by a message that 1 row has been affected, and when I view my Clock table it looks just like what I expect it to look like. What is the best way to acclompish this?

And my second part of this question is in my WHERE statements. Is there a function to get today's date in the clockDate column rather than have to populate today's date? Just trying to think ahead for building the front end application.

 IF NOT EXISTS(SELECT * FROM Clock WHERE clockDate = { fn CURRENT_DATE() }) AND userName = 'test')

Again, this gives me the results I want, but not until after getting an error "Error in WHERE clause near 'CURRENT_DATE'. Unable to parse query text."

I hope I have explained this properly, and thank you for your help!!

EDIT:

@RThomas @w00te

OK, so with the clockDate as a date field and breakOut as a time(0) field, should this work? Cause I'm still getting a "The Compound statement SQL construct or statement is not supported." Syntax error even though it seems to be working.

 IF NOT EXISTS (SELECT * FROM Clock WHERE (clockDate = GETDATE()) AND (userName = 'test')) BEGIN INSERT INTO Clock(clockDate, userName, breakOut) Values(GETDATE(), 'test', GETDATE()) END ELSE BEGIN UPDATE Clock SET breakOut = GETDATE() WHERE (clockDate = GETDATE()) AND (userName = 'test') END

My table results are:

clockDate userName clockIn breakOut breakIn clockOut 08/10/2012 test NULL 11:24:38 NULL NULL

This is the result I want but this error confuses me. Is this a Visual Studio error or a SQL error? And I'll read up on Merge Statements, thank you both for the links.

Improve this question
4
  • 13
    Check out the Merge statement. Commented Aug 10, 2012 at 17:32
  • 2
    Despite my answer below, I agree with the others about a merge statement being a great way to handle insert/update scenarios when you're not sure if the value is already there or not. Here is a walkthrough of the merge statement that I posted awhile back. stackoverflow.com/questions/10218101/… if you want to learn more about how it works. Commented Aug 10, 2012 at 18:06
  • This is what exactly MERGE statement is used for! Check it out technet.microsoft.com/en-us/library/bb510625.aspx Commented Aug 16, 2013 at 21:25
  • Note that in some cases, especially with large data sets, the merge statement can be prohibitively slow, causing a lot of tran log activity. So knowing how to logic it out is still valid. Commented Jun 10, 2018 at 22:53

4 Answers 4

Reset to default
70

At first glance your original attempt seems pretty close. I'm assuming that clockDate is a DateTime fields so try this:

IF (NOT EXISTS(SELECT * FROM Clock WHERE cast(clockDate as date) = '08/10/2012') AND userName = 'test') BEGIN INSERT INTO Clock(clockDate, userName, breakOut) VALUES(GetDate(), 'test', GetDate()) END ELSE BEGIN UPDATE Clock SET breakOut = GetDate() WHERE Cast(clockDate AS Date) = '08/10/2012' AND userName = 'test' END

Note that getdate gives you the current date. If you are trying to compare to a date (without the time) you need to cast or the time element will cause the compare to fail.

If clockDate is NOT datetime field (just date), then the SQL engine will do it for you - no need to cast on a set/insert statement.

IF (NOT EXISTS(SELECT * FROM Clock WHERE clockDate = '08/10/2012') AND userName = 'test') BEGIN INSERT INTO Clock(clockDate, userName, breakOut) VALUES(GetDate(), 'test', GetDate()) END ELSE BEGIN UPDATE Clock SET breakOut = GetDate() WHERE clockDate = '08/10/2012' AND userName = 'test' END

As others have pointed out, the merge statement is another way to tackle this same logic. However, in some cases, especially with large data sets, the merge statement can be prohibitively slow, causing a lot of tran log activity. So knowing how to logic it out as shown above is still a valid technique.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

clockDate is a 'date' data type NOT 'datetime'. Sorry for not clarifying that.
you can drop the cast statements then... I'll add to my post.
Oh and breakOut is a 'time(0)' field
That's ok. SET breakOut = GetDate() will still update to the current time. Just be aware that if you want to compare the values (instead of setting a value) you'll need to cast first like this... where cast(getdate() as time(0)) = breatOut.
To put it a different way. You can set date, datetime, and time to getdate(). The sql engine is smart enough to do the conversion work for you. It's just when comparing the value that you have to get more specific.
37

As others have suggested that you should look into MERGE statement but nobody provided a solution using it I'm adding my own answer with this particular TSQL construct. I bet you'll like it.

Important note

Your code has a typo in your if statement in not exists(select...) part. Inner select statement has only one where condition while UserName condition is excluded from the not exists due to invalid brace completion. In any case you cave too many closing braces.

I assume this based on the fact that you're using two where conditions in update statement later on in your code.

Let's continue to my answer...

SQL Server 2008+ support MERGE statement

MERGE statement is a beautiful TSQL gem very well suited for "insert or update" situations. In your case it would look similar to the following code. Take into consideration that I'm declaring variables what are likely stored procedure parameters (I suspect).

declare @clockDate date = '08/10/2012'; declare @userName = 'test'; merge Clock as target using (select @clockDate, @userName) as source (ClockDate, UserName) on (target.ClockDate = source.ClockDate and target.UserName = source.UserName) when matched then update set BreakOut = getdate() when not matched then insert (ClockDate, UserName, BreakOut) values (getdate(), source.UserName, getdate());
Improve this answer

2 Comments

I have some reserve in finding MERGE beautiful.
All good until you try to use MERGE on a remote table.. <face palm> Why!?!?
6 
IF NOT EXISTS(SELECT * FROM Clock WHERE clockDate = '08/10/2012') AND userName = 'test')

Has an extra parenthesis. I think it's fine if you remove it:

IF NOT EXISTS(SELECT * FROM Clock WHERE clockDate = '08/10/2012' AND userName = 'test')

Also, GETDATE() will put the current date in the column, though if you don't want the time you'll have to play a little. I think CONVERT(varchar(8), GETDATE(), 112) would give you just the date (not time) portion.

IF NOT EXISTS(SELECT * FROM Clock WHERE clockDate = CONVERT(varchar(8), GETDATE(), 112) AND userName = 'test')

should probably do it.

PS: use a merge statement :)

Improve this answer

Comments

2

You need to replace it as WHERE clockDate = { fn CURRENT_DATE() } AND userName = 'test'. Please remove extra ")" from { fn CURRENT_DATE() })

Improve this answer

1 Comment

Sorry this was just a typo on my part. I do have it correct in my SQL query window.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.