SQL存储过程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!

好。我之前得到了很多帮助，早些时候使用SQL后端来处理一个简单的...只是不适合我:( ...我工作的小办公室的时钟解决方案，所以我回来了！

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

我目前正在使用的表格由6列组成：

clockDate date not null PK
clockDate date not null PK
userName varchar(50) not null PK
userName varchar（50）not null PK
clockIn time(0)
clockIn time（0）
breakOut time(0)
breakOut时间（0）
breakIn time(0)
breakIn time（0）
clockOut time(0)
clockOut时间（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.

我虽然从上一个问题中找到了我的IF NOT EXISTS INSERT ELSE UPDATE语句，但现在我试图在存储过程中使用它，而不是简单的查询窗口，但没有成功。

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!

这是我的问题...如果用户DID时钟进入当天我得到主键违规，因为存储过程仍在尝试运行语句的INSERT部分并且从不运行UPDATE行。我已经尝试过用IF NOT EXISTS翻转同样的结果。让IF-ELSE在存储过程中工作的诀窍是什么？这可以通过我的思考方式完成，还是我必须研究Merge语句？我的计划是从每个工作站上的简单Visual Basic程序运行存储过程。也许我越过了我的脑袋:(对我的老板来说太糟糕了，只买一个时钟解决方案！

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!!!

这是正确的，还是可以进一步改进？再次非常感谢你！

4 个解决方案

#1

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

这可能是这里的问题：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:

GetDate返回当前日期和当前时间，这与clockDate不匹配。您可以将日期与DateDiff进行比较：

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

#2

Your problem would appear to be the following:

您的问题似乎如下：

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

让我们假设用户在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:

现在您的IF语句正在执行此操作：

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:

您还需要确保将clockDate存储为GETDATE（）的日期部分，否则，您需要调整查询，如下所示：

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

#3

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

您的更新永远不会运行，因为GETDATE会返回日期和时间。

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

#4

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

#1