DQ78qW6n

1-- If the address already exists, we will select its ID and store it in a variable.
2    BEGIN
3      SET @Address_ID =
4      (
5        SELECT TOP 1 [Address_ID]
6        FROM [Address]
7        WHERE
8          [Building] = @Building_Param
9          AND [Factory] = @Factory_Param
10      )
11    END
12	
13/**
14 * Inserts a new entry into the department table.
15 *
16 * @author     Felix Tietjen
17 */
18DECLARE @The_Date DATETIME2
19SET @The_Date = GETDATE()
20
21DECLARE @Department_Name_Param NVARCHAR(MAX)
22SET @Department_Name_Param = ?
23
24DECLARE @Department_Description_Param NVARCHAR(MAX)
25SET @Department_Description_Param = ?
26
27DECLARE @Factory_Param NVARCHAR(MAX)
28SET @Factory_Param = ?
29
30DECLARE @Building_Param NVARCHAR(MAX)
31SET @Building_Param = ?
32
33DECLARE @Address_ID BIGINT
34SET @Address_ID = 0
35
36-- If the address does not exist, we will need to create it automagically.
37IF NOT EXISTS (
38    SELECT *
39    FROM [Address]
40    WHERE
41      [Building] = @Building_Param
42      AND [Factory] = @Factory_Param
43)
44  BEGIN
45    INSERT INTO [Department] (
46      [Department_Name],
47      [Department_Description],
48      [Time_Created],
49      [Time_Modified]
50    )
51    OUTPUT INSERTED.[Address_ID] INTO @Address_ID
52    VALUES (
53      @Department_Name_Param,
54      @Department_Description_Param,
55      @The_Date,
56      @The_Date
57    )
58  END
59ELSE
60-- If the address already exists, we will select its ID and store it in a variable.
61    BEGIN
62      SET @Address_ID =
63      (
64        SELECT TOP 1 [Address_ID]
65        FROM [Address]
66        WHERE
67          [Building] = @Building_Param
68          AND [Factory] = @Factory_Param
69      )
70    END
71
72-- If the department does not exist, we will create it using the Address_ID provided by our previous operations.
73IF NOT EXISTS (SELECT * FROM [Department] WHERE [Department_Name] = @Department_Name_Param)
74BEGIN
75  INSERT INTO [Department] (
76    [Department_Name],
77    [Department_Description],
78    [Address_ID],
79    [Time_Created],
80    [Time_Modified]
81  )
82  VALUES (
83    @Department_Name_Param,
84    @Department_Description_Param,
85    @Address_ID,
86    @The_Date,
87    @The_Date
88  )
89END