C# SQL 存储过程 - 返回 ID

Question

我有以下 SP:

ALTER Procedure [dbo].[SP_RegisterComp]
     @CompName varchar(100)
     ,@Check_In datetime
     ,@ID int output

As
Set NoCount On

Update Host Set [Check_In]=@Check_In
                 Where [CompName]=@CompName 


If @@RowCount=0
  Insert into Host values (@CompName, @Check_In)
  SET @ID=SCOPE_IDENTITY()
  RETURN @ID

这很好用。如果表中已存在计算机名称，则更新 checkin 时间，否则它将创建新记录并返回 ID。

当记录更新时，我将如何获取@id？

干杯，

约翰

Answer 1

只需自己检查该行是否已经存在，而不是让 @@ROWCOUNT 告诉您是否需要插入。

ALTER PROCEDURE [dbo].[SP_RegisterComp]
     @CompName varchar(100)
     ,@Check_In datetime
     ,@ID int output

AS
SET NOCOUNT ON;

SELECT TOP 1 @Id = [IdField] FROM Host WHERE [CompName] = @CompName
   ORDER BY [Check_In] DESC

IF (@Id IS NOT NULL)
BEGIN
    UPDATE Host SET [Check_In]=@Check_In WHERE [IdField] = @Id
END
ELSE
BEGIN

    INSERT INTO Host VALUES (@CompName, @Check_In)
    SET @ID=SCOPE_IDENTITY()

END

RETURN @ID