Advertisement
bluebunny72

Friendly error message for FK

Jan 9th, 2018
226
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 0.96 KB | None | 0 0
  1. IF NOT EXISTS (SELECT TOP 1 1 FROM ObjectMainMessages WHERE ObjectName = 'FK_item_ILC_InfoCard' AND MessageType = 18)
  2.     BEGIN
  3.         DECLARE @NextMessageNo  int
  4.         DECLARE @NextMessageNoNext  int
  5.  
  6.         SELECT @NextMessageNo   = ISNULL(MAX(messageno),799999)
  7.         FROM ApplicationMessages
  8.         WHERE MessageNo BETWEEN 800000 AND 999999
  9.  
  10.         SET @NextMessageNo = @NextMessageNo + 1
  11.         SET @NextMessageNoNext = @NextMessageNo + 1
  12.  
  13.         ---add main message 404 = Cannot delete record. At least one &1 exists for this &2.
  14.         EXEC AddObjectMainMessageSp 'FK_item_ILC_InfoCard', 18, 404, 16, 'Item to InfoCard FK violation', 0
  15.  
  16.         --add the substitution text
  17.         EXEC AddApplicationMessageSp @NextMessageNo, 'Item', N'1033'
  18.         EXEC AddApplicationMessageSp @NextMessageNoNext, 'InfoCard', N'1033'
  19.  
  20.         --add substitution text to main message
  21.         EXEC AddObjectBuildMessageSp 'FK_item_ILC_InfoCard', 18, 1, @NextMessageNo
  22.         EXEC AddObjectBuildMessageSp 'FK_item_ILC_InfoCard', 18, 2, @NextMessageNoNext
  23.     END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement