CREATE OR ALTER PROCEDURE LD0SPConvertToSequence (@TableName VARCHAR(100), @LNRTYP DECIMAL(9))
AS
BEGIN
    DECLARE @ID INT = (SELECT ISNULL((SELECT LNR FROM LD0P0LNR WHERE LNRTYP=@LNRTYP), 1))
    DECLARE @SQL_Sequence NVARCHAR(4000)
    DECLARE @SequenceName NVARCHAR(200) = @TableName + '_Seq'
    DECLARE @GrantSequence NVARCHAR(400) = 'GRANT UPDATE ON ' + @SequenceName + ' TO LD0ROANV'

    IF NOT EXISTS (SELECT * FROM sys.sequences WHERE name = @SequenceName)
    BEGIN
        INSERT INTO LD0P0SequenceLog VALUES (@TableName, @ID, GETDATE())
        
        SET @SQL_Sequence = N'CREATE SEQUENCE ' + @SequenceName + ' 
            AS DECIMAL(9)
            START WITH ' + CAST(@ID AS NVARCHAR(20)) + '
            MINVALUE 0
            MAXVALUE 999999999
            INCREMENT BY 1
            CACHE 50'
        
        EXEC sp_executesql @SQL_Sequence
    END
    ELSE
    BEGIN
        PRINT 'Sequence ' + @SequenceName + ' already exists.'
    END

    EXEC sp_executesql @GrantSequence
END
GO

CREATE OR ALTER PROCEDURE LD0SPSequenceDefault 
    @TableName VARCHAR(100), 
    @DefaultColumn VARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL_default NVARCHAR(4000);
    DECLARE @SCHEMA VARCHAR(100) = (
        SELECT SCH.NAME
        FROM SYS.TABLES TAB
        JOIN SYS.SCHEMAS SCH ON SCH.schema_id = TAB.schema_id
        WHERE TAB.NAME = @TableName
    )

    IF NOT EXISTS (
        SELECT 1 
        FROM sys.default_constraints dc
        JOIN sys.columns c ON dc.parent_column_id = c.column_id AND dc.parent_object_id = c.object_id
        JOIN sys.tables t ON c.object_id = t.object_id
        JOIN sys.schemas s ON t.schema_id = s.schema_id
        WHERE t.name = @TableName 
        AND c.name = @DefaultColumn
        AND s.name = @SCHEMA
    )
    BEGIN
        SET @SQL_default = N'ALTER TABLE ' + QUOTENAME(@SCHEMA) + '.' + QUOTENAME(@TableName) + 
            ' ADD CONSTRAINT ' + QUOTENAME('DF_Seq_' + @TableName) +
            ' DEFAULT (NEXT VALUE FOR ' + QUOTENAME(@TableName + '_Seq') + ') FOR ' + QUOTENAME(@DefaultColumn);
        
        EXEC sp_executesql @SQL_default;
    END
    ELSE
    BEGIN
        PRINT 'DEFAULT-constraint finns redan för kolumn ' + @DefaultColumn + ' i tabell ' + @TableName;
    END
END
GO