OK. I got a lot of help here earlier working with a SQL backend to a simple ... just not for me :( ... time clock solution for the small office I work in, so I'm back for more!

My table I'm currently working with consists of 6 columns:

  1. clockDate date not null PK
  2. userName varchar(50) not null PK
  3. clockIn time(0)
  4. breakOut time(0)
  5. breakIn time(0)
  6. clockOut time(0)

I though I had figured out my IF NOT EXISTS INSERT ELSE UPDATE statement from my last question, but now I'm trying to use it in a Stored Procedure, rather than a plain query window, with no success.

Basically a user clocking in is a no-brainer. However, if the user doesn't clock in, but they clock out for lunch, the statement needs to create the row instead of updating an existing row. Ok so here's my stored procedure:

ALTER PROCEDURE dbo.BreakOut
(
    @userName varchar(50)
)
AS

IF EXISTS (SELECT * FROM Clock WHERE clockDate = GETDATE() AND userName = @userName)
    BEGIN
        UPDATE Clock SET breakOut = GETDATE() 
            WHERE clockDate = GETDATE() AND userName = @userName
    END
ELSE
    BEGIN
        INSERT INTO Clock (clockDate, userName, breakOut) 
            VALUES (GETDATE(), @userName, GETDATE())
    END

Here's my problem... If the user DID clock in for the day I get a primary key violation because the stored procedure is still trying to run the INSERT part of the statement and never runs the UPDATE line. I've tried it flipped with an IF NOT EXISTS as well with the same result. What's the trick to get IF-ELSE to work in a stored procedure? Can this be done they way I'm thinking or do I have to study Merge statement? My plan is to run the stored procedures from a simple Visual Basic program on each

sql stored-procedures if-statement
Edit
Report