CODEWITHSUNDEEP
c#sql-server
user3015544
user3015544

Reputation: 11

C# SQL stored procedure - returning ID

I have the following 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

This works fine. If a computer name already exists in the table then the check in time is updated else it will create a new record and return the ID.

How would I go about getting the @id when the record is updated?

Cheers,

John

Upvotes: 1

Views: 5375

Answers (4)

Charles Bretana
Charles Bretana

Reputation: 146449

  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.

Upvotes: 0

dougczar
dougczar

Reputation: 595

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

Upvotes: 0

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

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.

Upvotes: 2

Malk
Malk

Reputation: 11983

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

Upvotes: 3

Related Questions