spNextStudySequence.sql

CREATE PROCEDURE spNextStudySequence
    @StudyID int
AS
  DECLARE @temp table
	(
		ID bigint
	);
	
    UPDATE tStudy_Sequences
    SET Sequence = Sequence + 1
		OUTPUT INSERTED.Sequence into @temp
    WHERE StudyID = @StudyID;
    
    IF NOT EXISTS (SELECT 1 FROM @temp)
    BEGIN
		INSERT INTO tStudy_Sequences(StudyID, Sequence) VALUES(@StudyID, 1)
		INSERT INTO @temp VALUES(1)
	END
	
	SELECT TOP(1) * FROM @temp
GO