SQL存储过程IF EXISTS UPDATE ELSE INSERT [英] SQL stored procedure IF EXISTS UPDATE ELSE INSERT

问题描述

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

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!

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

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）

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)

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

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

这是我的问题...如果用户DID时钟进入当天我收到主键违规，因为存储过程仍在尝试运行 INSERT 部分语句，永远不会运行 UPDATE 行。我已尝试用 IF NOT EXISTS 翻转，并且结果相同。让IF-ELSE在存储过程中工作的诀窍是什么？这可以按照我的想法完成吗或者我必须学习合并声明？我的计划是从每个工作站上的简单Visual Basic程序运行存储过程。也许我已经超过了我的头脑:(糟糕的是我的老板太便宜而不能购买时钟解决方案！

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!

编辑：

谢谢大家的帮助!!我爱上了这个网站，问题得到的答案很快!!!这是我的工作存储过程：

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

推荐答案

这可能是问题所在：WHERE clockDate = GETDATE（）

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

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

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

这篇关于SQL存储过程IF EXISTS UPDATE ELSE INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！