请求编写SQL Server函数生成案件下一个docket序列编号

Let's build this function step by step, keeping your limited SQL experience in mind. The function will handle the multi-docket entries in the Docket field, extract the relevant root dockets, and compute the next sequence number efficiently.

Step 1: Create the Function #

Here's the complete function code. I'll break down each part below to make it clear:

CREATE FUNCTION dbo.MyFunction (@CaseType VARCHAR(2), @FiscalYear INT)
RETURNS INT
AS
BEGIN
    DECLARE @MaxSequence INT;

    -- Split multi-docket entries, extract root dockets, filter matches, and find max sequence
    SELECT @MaxSequence = MAX(CAST(RIGHT(root_docket, LEN(root_docket) - 5) AS INT))
    FROM (
        -- Extract root docket by removing the final "-XXX" subdocket part
        SELECT 
            PARSENAME(REPLACE(split_docket.value, '-', '.'), 3) + '-' + PARSENAME(REPLACE(split_docket.value, '-', '.'), 2) AS root_docket
        FROM MyCases
        -- Split space-separated docket numbers into individual rows
        CROSS APPLY STRING_SPLIT(Docket, ' ') AS split_docket
    ) AS root_dockets
    -- Filter for root dockets matching input case type and fiscal year
    WHERE 
        LEFT(root_docket, 2) = @CaseType
        AND SUBSTRING(root_docket, 3, 2) = CAST(@FiscalYear AS VARCHAR(2));

    -- Return 1 if no existing sequences, else return max sequence + 1
    RETURN ISNULL(@MaxSequence + 1, 1);
END;

Step 2: How It Works #

Let's break down each section to understand what's happening:

• Splitting Multi-Docket Entries:
  CROSS APPLY STRING_SPLIT(Docket, ' ') takes each space-separated docket in the Docket field and turns it into a separate row. This fixes the legacy database design where multiple dockets are stored in one field.

• Extracting Root Dockets:
  We replace - with . in each docket number, then use PARSENAME to grab the first two parts (before the final -XXX subdocket). For example:

  • PI14-6-000 becomes PI14.6.000 → PARSENAME(...,3) is PI14, PARSENAME(...,2) is 6 → combined into PI14-6 (the root docket).

• Filtering Matching Root Dockets:

  • LEFT(root_docket, 2) checks if the first two characters match your input @CaseType.
  • SUBSTRING(root_docket, 3, 2) grabs the next two characters (the fiscal year) and matches it to your input @FiscalYear.

• Calculating the Next Sequence:

  • RIGHT(root_docket, LEN(root_docket) - 5) extracts the sequence number (everything after the first 5 characters: XXYY- where XX is case type, YY is fiscal year).
  • We cast this to an integer, find the maximum value with MAX(), add 1, and use ISNULL to return 1 if there are no existing sequences for the case type/fiscal year.

Step 3: Test the Function #

Using your provided test data, let's verify the expected results:

-- Should return 8 (existing PI14 sequences: 1,2,3,4,6,7 → max 7 +1 =8)
SELECT dbo.MyFunction('PI', 14);

-- Should return 5 (existing PI15 sequences:1,2,3,4 → max 4 +1=5)
SELECT dbo.MyFunction('PI',15);

-- Test a case with no existing records (e.g., 'TS',16) → returns 1
SELECT dbo.MyFunction('TS',16);

Notes #

• Compatibility: STRING_SPLIT works in SQL Server 2016 and later. If you're using an older version, we can adjust to use a different splitting method—just let me know!
• Edge Cases: The function handles sequence numbers of any length (up to 4 digits as per your requirement) and correctly returns 1 when there are no existing entries for the given case type and fiscal year.

内容的提问来源于stack exchange，提问作者Emily Beth