SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.SequenceTable
(
    sequence_name   nvarchar(20) NOT NULL,
    next_value      integer NOT NULL,

    CONSTRAINT [PK dbo.SequenceTable sequence_name]
    PRIMARY KEY CLUSTERED (sequence_name),
);
INSERT dbo.SequenceTable
    (sequence_name, next_value)
VALUES
    (N'PermitIDs for 2016', 1),
    (N'PermitIDs for 2017', 1),
    (N'PermitIDs for 2018', 1);
3 rows affected

CREATE PROCEDURE dbo.Allocate_TSQL
 
    @SequenceName       nvarchar(20),   -- The name of the sequence to allocate keys from
    @RangeSize          integer = 1,    -- The number of keys to allocate
    @FirstAllocated     integer OUTPUT  -- The first key allocated (output)
 
AS
BEGIN
 
    SET XACT_ABORT ON;  -- Most errors will abort the batch
    SET NOCOUNT ON;     -- Supress 'x row(s) affected' messages
    SET ROWCOUNT 0;     -- Reset rowcount
 
    -- Validate the range size requested
    IF (@RangeSize IS NULL OR @RangeSize < 1)
    BEGIN
        RAISERROR('@RangeSize must be a positive integer (supplied value = %i)', 16, 1, @RangeSize);
        RETURN  999;
    END;
 
    -- Initialize the output parameter
    SET @FirstAllocated = NULL;
 
    -- Update the row associated with @SequenceName, returning the 
    -- current value, and then incrementing it by @RangeSize
    UPDATE dbo.SequenceTable WITH (READCOMMITTEDLOCK)
    SET @FirstAllocated = next_value,
        next_value = next_value + @RangeSize
    WHERE sequence_name = @SequenceName;
 
    -- If @Allocated has a non-NULL value, we know we successfully updated a row
    RETURN CASE WHEN (@FirstAllocated IS NOT NULL) THEN 0 ELSE -999 END; 
END;
CREATE TABLE dbo.ImportPermits
(
    ImportPermitID integer IDENTITY (1, 1)
        CONSTRAINT [PK dbo.ImportPermits ImportPermitID]
        PRIMARY KEY CLUSTERED,
    ImportPermitNo nchar(12) NULL,
    LodgementDate datetime NULL
);
CREATE TRIGGER ImportPermitsImportPermitNo
ON dbo.ImportPermits
AFTER INSERT AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN;   -- Return immediately if no rows affected
    SET XACT_ABORT, NOCOUNT ON; -- Most errors abort the batch; no row count messages
    SET ROWCOUNT 0;             -- Ensure all rows are visible (local to trigger)

    DECLARE 
        @rc integer,
        @FirstAllocated integer,
        @RowCount integer,
        @Years integer,
        @SeqName nvarchar(20);

    -- Count rows and distinct lodgement years in the insert set
    SELECT 
        @RowCount = COUNT(*),
        @Years = COUNT(DISTINCT(YEAR(INS.LodgementDate))),
        @SeqName = N'PermitIDs for ' + 
            CONVERT(nchar(4), MIN(YEAR(INS.LodgementDate)))
    FROM Inserted AS INS;

    -- Check for multiple lodgement years (not implemented)
    IF @Years > 1
    BEGIN
        RAISERROR('Multiple LodgementDate years are not supported.', 16, 1);
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        RETURN;        
    END;

    -- Allocate the range of sequence numbers we will need
    EXECUTE @rc = dbo.Allocate_TSQL
        @SequenceName = @SeqName,
        @RangeSize = @RowCount,
        @FirstAllocated = @FirstAllocated OUTPUT;

    IF @rc <> 0
    BEGIN
        RAISERROR('Sequence allocation failed.', 16, 1);
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        RETURN;
    END;

    -- Assign ImportPermitNo values using the sequence numbers allocated
    WITH Sequenced AS
    (
        SELECT
            IP.ImportPermitNo,
            IP.LodgementDate,
            Seq = 
                @FirstAllocated - 1 + 
                ROW_NUMBER() OVER (
                    ORDER BY IP.LodgementDate ASC)
        FROM Inserted AS INS
        JOIN dbo.ImportPermits AS IP
            ON IP.ImportPermitID = INS.ImportPermitID
    )
    UPDATE Sequenced
    SET Sequenced.ImportPermitNo = 
        N'IP' + 
        CONVERT(nchar(4), YEAR(Sequenced.LodgementDate)) +
        RIGHT(N'000000' + CONVERT(nvarchar(11), Sequenced.Seq), 6);
END;
-- Various inserts
INSERT dbo.ImportPermits
    (LodgementDate) 
VALUES 
    ('20170103');

INSERT dbo.ImportPermits 
    (LodgementDate) 
VALUES 
    ('20170102');

INSERT dbo.ImportPermits 
    (LodgementDate) 
VALUES 
    ('20170104'),
    ('20170101'),
    ('20170105');

INSERT dbo.ImportPermits 
    (LodgementDate) 
VALUES 
    ('20160102'),
    ('20160103'),
    ('20160101');

INSERT dbo.ImportPermits 
    (LodgementDate) 
VALUES 
    ('20180102'),
    ('20180103'),
    ('20180101');
11 rows affected

SELECT
    IP.ImportPermitID,
    IP.ImportPermitNo,
    IP.LodgementDate
FROM dbo.ImportPermits AS IP;


ImportPermitID
ImportPermitNo
LodgementDate


1
IP2017000001
03/01/2017 00:00:00

2
IP2017000002
02/01/2017 00:00:00

3
IP2017000004
04/01/2017 00:00:00

4
IP2017000003
01/01/2017 00:00:00

5
IP2017000005
05/01/2017 00:00:00

6
IP2016000002
02/01/2016 00:00:00

7
IP2016000003
03/01/2016 00:00:00

8
IP2016000001
01/01/2016 00:00:00

9
IP2018000002
02/01/2018 00:00:00

10
IP2018000003
03/01/2018 00:00:00

11
IP2018000001
01/01/2018 00:00:00
ImportPermitID	ImportPermitNo	LodgementDate
1	IP2017000001	03/01/2017 00:00:00
2	IP2017000002	02/01/2017 00:00:00
3	IP2017000004	04/01/2017 00:00:00
4	IP2017000003	01/01/2017 00:00:00
5	IP2017000005	05/01/2017 00:00:00
6	IP2016000002	02/01/2016 00:00:00
7	IP2016000003	03/01/2016 00:00:00
8	IP2016000001	01/01/2016 00:00:00
9	IP2018000002	02/01/2018 00:00:00
10	IP2018000003	03/01/2018 00:00:00
11	IP2018000001	01/01/2018 00:00:00