title: SPROC that returns unique calculated INT for each call
body: <p>I'm implementing in my application an event logging system to save some event types from my code, so I've created a table to store the log type and an Incremental ID:</p>
  
  <pre><code>|LogType|CurrentId|
  |info   |    1    |
  |error  |    5    |
  </code></pre>
  
  <p>And also a table to save the concrete log record</p>
  
  <pre><code>|LogType|IdLog|Message        |
  |info   |1    |Process started|
  |error  |5    |some error     |
  </code></pre>
  
  <p>So, every time I need to save a new record I call a SPROC to calculate the new id for the log type, basically: <code>newId = (currentId + 1)</code>. But I am facing an issue with that calculation because if multiple processes calls the SPROC at the same time the "generated Id" is the same, so I'm getting log records with the same Id, and every record must be Id-unique.</p>
  
  <p>This is my SPROC written for <strong>SQL Server 2005</strong>:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[usp_GetLogId]
      @LogType VARCHAR(MAX)
  AS
  BEGIN
  
      SET NOCOUNT ON;
  
      BEGIN TRANSACTION
  
      BEGIN TRY
  
      DECLARE @IdCreated VARCHAR(MAX)
  
      IF EXISTS (SELECT * FROM TBL_ApplicationLogId WHERE LogType = @LogType)
      BEGIN
          DECLARE @CurrentId BIGINT
          SET @CurrentId = (SELECT CurrentId FROM TBL_ApplicationLogId WHERE LogType = @LogType)
  
          DECLARE @NewId BIGINT
          SET @NewId = (@CurrentId + 1)
  
          UPDATE TBL_ApplicationLogId
          SET CurrentId = @NewId
          WHERE LogType = @LogType
  
          SET @IdCreated = CONVERT(VARCHAR, @NewId)
      END
      ELSE
      BEGIN
          INSERT INTO TBL_ApplicationLogId VALUES(@LogType, 0)
  
          EXEC @IdCreated = usp_GetLogId @LogType
      END
  
      END TRY
      BEGIN CATCH
  
      DECLARE @ErrorMessage NVARCHAR(MAX)
      SET @ErrorMessage = ERROR_MESSAGE()
  
      IF @@TRANCOUNT &gt; 0
          ROLLBACK TRANSACTION;
  
      RAISERROR (@ErrorMessage, 16, 1)
  
      END CATCH
  
      IF @@TRANCOUNT &gt; 0
          COMMIT TRANSACTION
  
      SELECT @IdCreated
  END
  </code></pre>
  
  <p>I would appreciate your help to fix the sproc to return an unique id on every call.</p>
  
  <p>It has to work on <strong>SQL Server 2005</strong>. Thanks</p>