Mike Jervis was maintaining a case management system and came across a rather peculiar workaround involving a stored procedure and a COM method. Both were checked in to source control around the same time. Both were created for the same feature on the same release. Both were "owned" and developed by the same programmer. Both exclusively call each other. This of course raises the age old question, what came first, the stored proc or the COM?

Stored Procedure Code:

CREATE PROCEDURE sp_Scheduling_CaseSequenceNum
(
  @sequenceNum INT = NULL OUT 
) AS BEGIN

  SET NOCOUNT ON
  BEGIN TRANSACTION

  INSERT INTO Scheduling_Case_Num (Num_Date) VALUES(GETDATE())
  -- COM method increments the value passed back
  SET @sequenceNum = @@IDENTITY - 1

  SELECT @sequenceNum AS CaseSequenceNum
  COMMIT
  RETURN @sequenceNum
END

COM component code

rsCaseNum = ExecStoredProc("sp_Scheduling_CaseSequenceNum")
If rsCaseNum.EOF Then
  Err.Raise Errors.Unclassified, _
    "ScheduleSys.CaseMgr::NewCase", "Unexpected EOF."
End If

'procedure decrements the new case num
intNewCaseSequenceNum = rsCaseNum("CaseSequenceNum") + 1
[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!