Advertisement
bluebunny72

Friendly error message for PK

May 31st, 2016
227
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.79 KB | None | 0 0
  1. IF NOT EXISTS (SELECT TOP 1 1 FROM ObjectMainMessages WHERE ObjectName = 'PK_ILC_UserRole' AND MessageType = 17)
  2.     BEGIN
  3.         DECLARE @NextMessageNo  int
  4.  
  5.         SELECT @NextMessageNo   = ISNULL(MAX(messageno),799999)
  6.         FROM ApplicationMessages
  7.         WHERE MessageNo BETWEEN 800000 AND 999999
  8.  
  9.         SET @NextMessageNo = @NextMessageNo + 1
  10.  
  11.         --add main message 100093 = The &1 entered already exists.
  12.         EXEC AddObjectMainMessageSp 'PK_ILC_UserRole', 17, 100093, 16, 'ILC_UserRole PK violation', 0
  13.  
  14.         --add the substitution text
  15.         EXEC AddApplicationMessageSp @NextMessageNo, 'Role', N'1033'
  16.  
  17.         --add substitution text to main message
  18.         EXEC AddObjectBuildMessageSp 'PK_ILC_UserRole', 17, 1, @NextMessageNo
  19. END
  20.  
  21. --composite key example
  22. IF NOT EXISTS (SELECT TOP 1 1 FROM ObjectMainMessages WHERE ObjectName = 'PK_ILC_ItemWhse' AND MessageType = 17)
  23.     BEGIN
  24.         DECLARE @NextMessageNo  int
  25.         DECLARE @NextMessageNoNext  int
  26.  
  27.         SELECT @NextMessageNo   = ISNULL(MAX(messageno),799999)
  28.         FROM ApplicationMessages
  29.         WHERE MessageNo BETWEEN 800000 AND 999999
  30.  
  31.         SET @NextMessageNo = @NextMessageNo + 1
  32.         SET @NextMessageNoNext = @NextMessageNo + 1
  33.  
  34.         --Message='The Whse, Pending Item combination entered already exists.'
  35.  
  36.         --add existing main message for PK_ILC_ItemWhse key
  37.         --100094 = The &1, &2 combination entered already exists
  38.         EXEC AddObjectMainMessageSp 'PK_ILC_ItemWhse', 17, 100094, 16, 'Pending Item Item Whse PK Violation', 0
  39.  
  40.         --add the substitution text
  41.         EXEC AddApplicationMessageSp @NextMessageNo, 'Whse', N'1033'
  42.         EXEC AddApplicationMessageSp @NextMessageNoNext, 'Pending Item', N'1033'
  43.  
  44.         --add substitution text to main message.
  45.         EXEC AddObjectBuildMessageSp 'PK_ILC_ItemWhse', 17, 1, @NextMessageNo
  46.         EXEC AddObjectBuildMessageSp 'PK_ILC_ItemWhse', 17, 2, @NextMessageNoNext
  47.     END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement