在插入之前查找行是否存在的存储过程

Question

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SS_InsertApplicationStatus] 
@CustomerID int,
@EventType nvarchar,
@EventDateTime datetime,
@Comments nvarchar,
@EmployeeID varchar,
@ApplicationDate date
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXIST(SELECT COUNT(*) FROM [dbo].[SS_CustomerCard] 
WHERE SS_ID = '@SS_ID')
BEGIN
INSERT INTO SS_ApplicationStatus (CustomerID, EventType, EventDateTime, Comments, EmployeeID, ApplicationDate)
VALUES (@CustomerID, @EventType, @EventDateTime, @Comments, @EmployeeID, @ApplicationDate)
END
END

IF NOT EXIST;这是我从MySQL复制的。

我要做的是

• 如果没有ROW存在，那么我想执行2次插入，如下所示:

  -- Insert statements for procedure here
  INSERT INTO SS_ApplicationStatus (CustomerID, EventType, EventDateTime, Comments, EmployeeID, ApplicationDate)
  VALUES (@CustomerID, "Application Submission", @EventDateTime, @Comments, @EmployeeID, @ApplicationDate)
  INSERT INTO SS_ApplicationStatus (CustomerID, EventType, EventDateTime, Comments, EmployeeID, ApplicationDate)
  VALUES (@CustomerID, @EventType, @EventDateTime, @Comments, @EmployeeID, @ApplicationDate)
  

• 如果ROW确实存在

  -- Insert statements for procedure here
  INSERT INTO SS_ApplicationStatus (CustomerID, EventType, EventDateTime, Comments, EmployeeID, ApplicationDate)
  VALUES (@CustomerID, @EventType, @EventDateTime, @Comments, @EmployeeID, @ApplicationDate)
  

Answer 1

为什么使用过程逻辑?让它完全凝固。当然，你需要解释@SS_ID来自哪里，因为它不存在于你的代码片段中。

INSERT INTO SS_ApplicationStatus (CustomerID, EventType, EventDateTime, Comments, EmployeeID, ApplicationDate)
SELECT @CustomerID, @EventType, @EventDateTime, @Comments, @EmployeeID, @ApplicationDate
WHERE NOT EXISTS (SELECT 1 FROM [dbo].[SS_CustomerCard] WHERE SS_ID = @SS_ID);

SQL Server:

• 关键字是EXISTS而不是EXIST
• 你没有引用参数，所以应该是@SS_ID而不是'@SS_ID'

Answer 2

下面的语句有逻辑错误

IF NOT EXIST(SELECT COUNT(*) FROM [dbo].[SS_CustomerCard] WHERE SS_ID='@SS_ID')

你在查询中使用聚合函数COUNT(*)，它将始终返回单行，基于你的WHERE条件的数值1或0。