Pasted

IF NOT EXISTS (SELECT TOP 1 1 FROM ObjectMainMessages WHERE ObjectName = 'PK_ILC_UserRole' AND MessageType = 17)
    BEGIN
        DECLARE @NextMessageNo  int
 
        SELECT @NextMessageNo   = ISNULL(MAX(messageno),799999) 
        FROM ApplicationMessages 
        WHERE MessageNo BETWEEN 800000 AND 999999
 
        SET @NextMessageNo = @NextMessageNo + 1
 
        --add main message 100093 = The &1 entered already exists. 
        EXEC AddObjectMainMessageSp 'PK_ILC_UserRole', 17, 100093, 16, 'ILC_UserRole PK violation', 0 
 
        --add the substitution text
        EXEC AddApplicationMessageSp @NextMessageNo, 'Role', N'1033'
 
        --add substitution text to main message
        EXEC AddObjectBuildMessageSp 'PK_ILC_UserRole', 17, 1, @NextMessageNo
END
 
--composite key example
IF NOT EXISTS (SELECT TOP 1 1 FROM ObjectMainMessages WHERE ObjectName = 'PK_ILC_ItemWhse' AND MessageType = 17)
    BEGIN
        DECLARE @NextMessageNo  int
        DECLARE @NextMessageNoNext  int
 
        SELECT @NextMessageNo   = ISNULL(MAX(messageno),799999) 
        FROM ApplicationMessages 
        WHERE MessageNo BETWEEN 800000 AND 999999
 
        SET @NextMessageNo = @NextMessageNo + 1
        SET @NextMessageNoNext = @NextMessageNo + 1
 
        --Message='The Whse, Pending Item combination entered already exists.'
 
        --add existing main message for PK_ILC_ItemWhse key 
        --100094 = The &1, &2 combination entered already exists
        EXEC AddObjectMainMessageSp 'PK_ILC_ItemWhse', 17, 100094, 16, 'Pending Item Item Whse PK Violation', 0 
 
        --add the substitution text
        EXEC AddApplicationMessageSp @NextMessageNo, 'Whse', N'1033'
        EXEC AddApplicationMessageSp @NextMessageNoNext, 'Pending Item', N'1033'
 
        --add substitution text to main message.
        EXEC AddObjectBuildMessageSp 'PK_ILC_ItemWhse', 17, 1, @NextMessageNo
        EXEC AddObjectBuildMessageSp 'PK_ILC_ItemWhse', 17, 2, @NextMessageNoNext
    END