C# SQL stored procedure - returning ID

Just check if the row already exists yourself instead of letting @@ROWCOUNT tell you if the insert is needed.

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

You would setup an Output parameter. The code might look like this:

using (SqlConnection c = new SqlConnection(cString))
using (SqlCommand cmd = new SqlCommand("SP_RegisterComp", c))
{
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@CompName", compName);
    cmd.Parameters.AddWithValue("@Check_In", checkIn);

    var outParm = new SqlParameter("@ID", SqlDbType.Int);
    outParm.Direction = ParameterDirection.Output;

    cmd.ExecuteNonQuery();

    var val = outParm.Value;
}

As Aaron suggested, this is a very good candidate for locking, and transactional programming is always a best practice here as well. So, the modified code might look like this:

lock (_lockObj)
{
    using (SqlConnection c = new SqlConnection(cString))
    using (SqlTransaction tran = c.BeginTransaction())
    using (SqlCommand cmd = new SqlCommand("SP_RegisterComp", c, tran))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@CompName", compName);
        cmd.Parameters.AddWithValue("@Check_In", checkIn);

        var outParm = new SqlParameter("@ID", SqlDbType.Int);
        outParm.Direction = ParameterDirection.Output;

        cmd.ExecuteNonQuery();

        // commit the transaction
        tran.Commit();

        var val = outParm.Value;
    }
}

where _lockObj is defined as private object _lockObj = new Object(); as a field.

NOTE: you don't need to worry about the Rollback here because if an exception is raised the Rollback will be issued during the Dispose of the SqlTransaction object.

You should check if the record exists:

If EXISTS (SELECT * FROM Host WHERE [CompName] = @CompName)
    BEGIN
      BEGIN TRAN
          SELECT @ID=ID FROM Host WITH(UPDLOCK)
          WHERE Comp_Name = @Comp_Name

          UPDATE Host
          SET [Check_In] = @Check_In
          WHERE [Comp_Name] = @Comp_Name
          RETURN @ID
      COMMIT TRAN
    END
ELSE
    BEGIN
      Insert into Host values (@CompName, @Check_In)
      SET @ID=SCOPE_IDENTITY()
      RETURN @ID
END

1. You don't need it then, you sent it in...

2. Since you must have sent it in, it is already in the @Id t-sql variable, which is an output variable so it is accessible in the client ado.net code

3. I like the following pattern

     Alter Procedure [dbo].[SP_RegisterComp]
     @CompName varchar(100),
     @Check_In datetime.
     @ID int = null
     As
     Set NoCount On
   
       If @Id is null 
       Begin
          Insert ...
          SET @ID=SCOPE_IDENTITY()
       End
       Else Begin
         Update ...
         Where id = @Id
       End
   
       Select @Id id
     Return 0
   

Then in client code, you will get a single row, single column resultset (an ADO.Net table) with the id value in it, in all cases.