CODEWITHSUNDEEP
sqlstored-proceduresif-statement
tmhalbert
tmhalbert

Reputation: 1113

SQL stored procedure IF EXISTS UPDATE ELSE INSERT

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 workstation. Maybe I'm getting in over my head :( To bad my boss is too cheap to just buy a time clock solution!

EDIT:

Thank you ALL for your help!! I'm falling in love with this site, questions get answers SO FAST!!! Here is my working stored procedure:

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

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

Is this proper, or could it be improved more? Again Thank You ALL SO MUCH!!!

Upvotes: 21

Views: 94079

Answers (4)

Steves Kamdem
Steves Kamdem

Reputation: 1

CREATE PROCEDURE `SP_GENRE_SELECT`(
    IN _Id INTEGER,
      IN _Name VARCHAR(50),
      IN _account VARCHAR (50),
      IN _Password VARCHAR (50),
      IN _LastConnexionDate DATETIME,
      IN _CreatedDate DATETIME,
      IN _UpdatedDate DATETIME,
      IN _CreatedUserId INTEGER,
      IN _UpdatedUserId INTEGER,
      IN _Status TINYINT
    )
BEGIN
      SELECT *
      FROM user
      WHERE Id LIKE IF(_Id IS NULL,'%',CAST(_Id AS VARCHAR(50)))
      AND
      Name LIKE IF(_Name IS NULL,'%',CONCAT('%',_Name,'%'))
      AND
      Account LIKE IF(_Account IS NULL,'%',CONCAT('%',_Account,'%'))
      AND
      LastConnexionDate LIKE IF(_LastConnexionDate IS NULL,'%',CONCAT('%',CAST(LastConnexionDate AS VARCHAR(50),'%')))
      AND
      CreatedDate LIKE IF(_CreatedDate IS NULL,'%',CONCAT('%',CAST(_CreatedDate AS VARCHAR(50),'%')))
      AND
      UpdatedDate LIKE IF(_UpdatedDate IS NULL,'%',CONCAT('%',CAST(_UpdatedDate AS VARCHAR(50),'%')))
      AND
      CreatedUserID LIKE IF(_CreatedUserID IS NULL,'%',CONCAT('%',CAST(_CreatedUserID AS VARCHAR(50),'%')))
      AND
      UpdatedUserID LIKE IF(_UpdatedUserID IS NULL,'%',CONCAT('%',CAST(_UpdatedUserID AS VARCHAR(50),'%')))
      AND
      Status LIKE IF(_Status IS NULL,'%',CAST(_Status AS VARCHAR(50),'%'))

END

Upvotes: 0

dash
dash

Reputation: 91472

Your problem would appear to be the following:

Let's imagine the user clocked in at 09:00

A record like the following might exist:

ClockDate    userName    clockIn    breakOut    breakIn    clockOut
12/08/2012   joe         09:00      NULL        NULL       NULL

Now your IF statement is doing this:

SELECT * FROM Clock WHERE clockDate = "20120812 17:24:13" AND userName = @userName

i.e. this record wont exist.

Instead, try this:

IF EXISTS (SELECT * FROM Clock  WHERE clockDate = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) AND userName = @userName)

You also need to make sure you are storing clockDate as just the date portion of GETDATE(), otherwise, you would need to adjust your query like so:

IF EXISTS (SELECT * FROM Clock WHERE DATEADD(D, 0, DATEDIFF(D, 0, clockDate)) = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) AND userName = @userName)

Upvotes: 4

Dave R.
Dave R.

Reputation: 7304

Your update will never run because GETDATE returns a date and time.

http://msdn.microsoft.com/en-us/library/ms188383.aspx

Upvotes: 1

Jim
Jim

Reputation: 3510

This is probably the problem right here: WHERE clockDate = GETDATE()

GetDate returns the current date AND the current time, which wouldn't match up with clockDate. You can compare the dates with DateDiff instead:

WHERE DateDiff(dd, GetDate(),clockDate) = 0

Upvotes: 11

Related Questions