stored procedure with incremental numbers.

replied on August 31, 2021 • Show version history

So we had to make some assumptions.

Your sequential number will be 1 through 999 and then reset back to 1
Your first set of letters is user definable through a variable up to 40 characters long
Your dropdown will be up to 40 characters long

First we start by creating a Sequence in your SQL DB

CREATE SEQUENCE dbo.AlcosebaSequence
      AS INT
      START WITH 1
      INCREMENT BY 1
      MINVALUE 1
      MAXVALUE 999
      CYCLE
      NO CACHE;
GO

Now we can call the next value from the sequence, so we create our Stored Procedure like:

CREATE PROCEDURE dbo.spAlcosebaSequence
	@Leading VARCHAR(40),
	@DropDown VARCHAR(40)
AS
BEGIN
	SELECT (@Leading + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-' + RIGHT('00' + CAST(MONTH(GETDATE()) AS VARCHAR(2)),2) + '-' + @DropDown + '-' + RIGHT('000' + CAST(NEXT VALUE FOR dbo.AlcosebaSequence AS VARCHAR(3)),3)) AS AlcosebaSequence;
END

1 0

View 3 previous replies

replied on August 31, 2021 • Show version history

Hi Bert,

Thank you for your reply. I have a question.

How about the value of dropdown field?

How to store it on stored procedure?

I mean, how to configure the dropdown field so that the selected value will store to the "@DropDown" in create a procedure?

Cheers!

0 0

replied on September 1, 2021

It depends on where you want to create the value from the SP, but I am assuming that you want it on your form.

I have created a simple form with a single line input field for the leading value, a dropdown field, and a single line input field for the SP result:

Then I added a Lookup rule

and then when I use the form, it populates the results field when the Leading and dropdown fields are completed.

0 0

replied on September 1, 2021 • Show version history

Hi Bert,

Thank you.

What will be the code if "@leading" is fixed to the value of CWPO?

Cheers!

0 0

replied on September 2, 2021 • Show version history

You can either change the Stored Procedure or you could create a hidden field with "CWPO" as the default value.

To change the SP, first we need to drop/delete the existing SP and then recreate it with:

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'spAlcosebaSequence')
DROP PROCEDURE dbo.spAlcosebaSequence;
GO
CREATE PROCEDURE dbo.spAlcosebaSequence
    @DropDown VARCHAR(40)
AS
BEGIN
    SELECT ('CWPO-' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-' + RIGHT('00' + CAST(MONTH(GETDATE()) AS VARCHAR(2)),2) + '-' + @DropDown + '-' + RIGHT('000' + CAST(NEXT VALUE FOR dbo.AlcosebaSequence AS VARCHAR(3)),3)) AS AlcosebaSequence;
END

0 0

replied on September 3, 2021

Hi Bert,

Thanks for this.

Cheers!

0 0

replied on September 29, 2021 • Show version history

Hi @████████

I try the code and it works fine. But I recognize that when I change the value of @DropDown, the value of incremental number change, also when the month and year change, it's not reset to "000".

This scenario I'd like to do in Stored procedure.

1. The incremental value "000" not be changed even the drop down value is changed.

Scenario: I select a value @DropDown field: WKI value and Stored the value in Stored procedure and shows the final value in a single field like this "CWPO-2021-08-WKI-001", before I submit the form I change the value of the @DropDown field to IPC because I selected a wrong value, the final value in a single field after I change the value of @DropDown field must like this "CWPO-2021-08-IPC-001", the incremental value must not be change in any selected value of @DropDown field.

2. The incremental value must reset to beginning if the month change and year change.

3. The next incremental value is stored if a new form is generated or started by a user and a user select a value @DropDown field, even the user change the value of @DropDown field, the incremental value must not be changed if the form is not submitted.

It is possible to do this Stored procedure?

Hoping you can help me.

Cheers!

0 0

Question

Question

stored procedure with incremental numbers.

Replies

Sign in to reply to this post.