On one of our forms, we have a stored procedure that generates unique, sequential purchase order numbers when the form loads. In addition to returning the generated number from a SQL Sequence Object (and prepending it with 'CN'), the stored procedure also puts it into a table, along with the date, so that we can reference it later if need be:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetPONumberSequence] 
AS
BEGIN
    SET NOCOUNT ON;

	DECLARE @sequence nvarchar(50)
	SET @sequence = 'CN' + Cast((NEXT VALUE FOR dbo.PONumberSequence) as nvarchar(50))
   
    INSERT INTO [dbo].[PONumbersSequence]
           (LastUsedPONumber)
    VALUES
           (@sequence)
    SELECT TOP 1 [LastUsedPONumber] FROM dbo.PONumbersSequence 
		ORDER BY "DateInserted" DESC 
END

GO

This has been working great.

Recently, we saw the need to keep track of which Forms user is generating the purchase order number. Forms has the ability to pass parameters back to a stored procedure (it uses the same code, according to Robert Gordon), so we created a new stored procedure that takes in a @User parameter:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetPONumberSequenceWithUser]
    @User nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;

	DECLARE @sequence nvarchar(50)
	SET @sequence = 'CN' + Cast((NEXT VALUE FOR dbo.PONumberSequenceWithUser) as nvarchar(50))
   
    INSERT INTO [dbo].[PONumbersSequenceWithUser]
           (LastUsedPONumberWithUser, Submitter)
    VALUES
           (@sequence, @User)
    SELECT TOP 1 [LastUsedPONumberWithUser] FROM dbo.PONumbersSequenceWithUser 
		ORDER BY "DateInserted" DESC 
END

GO

We then updated the Lookup Rule in Forms to pass the User field's value as the User variable.

The issue is that now the stored procedure executes twice. We know this because the sequence object is incremented twice, and the record table has two entries:

The rows with "Travis Dyer" as the submitter are inserted when the stored procedure is executed by Forms. You'll note that the DateInserted column shows pairs of records within a second or less of each other, indicating that it ran twice.

There is also a "test1" row, which was created by running the stored procedure from SQL Management Studio. No duplicate rows were generated that way, so we have a good suspicion that this is a Forms bug.

Our project is dead in the water due this problem and we'd like some input from Laserfiche if possible.