1. CREATE OR ALTER PROCEDURE SpRikeshImportTsk
2. @Entity VARCHAR(50)
3. AS
4. BEGIN
5.  SET NOCOUNT ON;
6.  BEGIN TRY
7.  
8.  DECLARE @JsonData NVARCHAR(MAX);
9.  DECLARE @Data NVARCHAR(MAX);
10.  IF @Entity NOT IN ('Person', 'Organization')
11.   RAISERROR('Sorry invalid entity' , 16, 1);
12.  IF @Entity = 'Person'
13.   BEGIN
14.    
15.    CREATE TABLE #TempPerson (FirstName VARCHAR(250), MiddleName VARCHAR(250), LastName VARCHAR(250), SSN VARCHAR(50), Phone VARCHAR(25),
16.    Website VARCHAR(1000), [Status] VARCHAR(50), Address1 VARCHAR(100),Address2 VARCHAR(100),City VARCHAR(100),[State] VARCHAR(100),ZipCode VARCHAR(100),
17.    OfficeId INT
18.    );
19.    INSERT INTO #TempPerson (FirstName, MiddleName, LastName, SSN, Status, Website, Phone, Address1, Address2, City, State, ZipCode, OfficeId)
20.    VALUES
21.    ('Jake', 'John', 'Dough', '111-06-0000', 'Active', 'jmd.com', '9844644432', '123 Main Street', 'Apt 101', 'New York', 'NY', '10001', '1'),
22.    ('Alice', 'Grace', 'Smith', '111-22-0000', 'Active', 'ags.com', '9876543210', '456 Elm Street', 'Apt 202', 'Los Angeles', 'CA', '90001', '2'),
23.    ('Bob', 'William', 'Johnson', '333-33-3333', 'Active', 'bwj.com', '8765432109', '789 Oak Avenue', 'Apt 303', 'Chicago', 'IL', '60601', '3'),
24.    ('Emily', 'Jane', 'Davis', '444-44-4444', 'Active', 'ejd.com', '7654321098', '101 Pine Road', 'Apt 404', 'Miami', 'FL', '33101', '4'),
25.    ('Michael', 'Lee', 'Brown', '555-55-5555', 'Active', 'mlb.com', '6543210987', '202 Cedar Lane', 'Apt 505', 'Seattle', 'WA', '98101', '5'),
26.    ('Sarah', 'Marie', 'Clark', '666-66-6666', 'Active', 'smc.com', '5432109876', '303 Maple Drive', 'Apt 606', 'Atlanta', 'GA', '30301', '6'),
27.    ('David', 'Andrew', 'Wilson', '777-77-7777', 'Active', 'daw.com', '4321098765', '303 Maple Drive', 'Apt 707', 'Houston', 'TX', '77001', '7'),
28.    ('Olivia', 'Rose', 'Taylor', '888-88-8888', 'Active', 'ort.com', '3210987654', '505 Cherry Lane', 'Apt 808', 'Denver', 'CO', '80201', '8'),
29.    ('Ethan', 'Michael', 'White', '999-99-9999', 'Active', 'emw.com', '2109876543', '606 Birch Road', 'Apt 909', 'San Francisco', 'CA', '41010', '9');
30.  
31.  
32.  
33.    
34.    IF EXISTS (SELECT 1 FROM #TempPerson WHERE TRIM(ISNULL(FirstName, '')) = '' OR TRIM(ISNULL(LastName, '')) = '')
35.     RAISERROR('Sorry firstname or lastname cannot be null or empty', 16, 1);
36.  
37.  
38.    IF EXISTS (SELECT 1 FROM #TempPerson AS T WHERE ISNULL(T.Address1, '') = ''  OR ISNULL(T.City, '') = '' OR ISNULL(T.State, '') = '' OR ISNULL(T.ZipCode, '') = '')
39.     RAISERROR('Sorry other address field cannot be empty except Address2', 16, 1);
40.  
41.    IF EXISTS ( SELECT 1 FROM #TempPerson AS T WHERE LEN(T.ZipCode) <> 5)
42.     RAISERROR('Sorry the zip code should be only 5 digits', 16, 1);
43.  
44.  
45.    IF EXISTS(SELECT 1 FROM #TempPerson AS T INNER JOIN Person AS P ON P.SSN = T.SSN)
46.     BEGIN
47.      DECLARE @DuplicateSsn VARCHAR(MAX);
48.      SELECT @DuplicateSsn = T.SSN FROM #TempPerson AS T INNER JOIN Person AS P ON P.SSN = T.SSN;
49.      SET @DuplicateSsn  = 'Sorry the person already exists in the person table ' + 'Duplicate SSN '  + @DuplicateSsn;
50.      RAISERROR(@DuplicateSsn, 16, 1);
51.     END
52.  
53.    
54.    
55.  
56.    IF EXISTS(SELECT 1 FROM #TempPerson AS T WHERE LEN(T.Phone) <> 10)
57.     RAISERROR('Mobile numbers should be 10 digit only', 16, 1)
58.    
59.  
60.    IF EXISTS(SELECT 1 FROM #TempPerson AS T WHERE T.Status NOT IN ('Active', 'Inactive'))
61.     RAISERROR('Sorry the status field can only hold ''active'' or ''inactive'' as a status', 16, 1);
62.  
63.    SELECT @JsonData = (SELECT 1 AS 'UserPersonId',
64.    'Employee' AS 'Entity' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER);
65.  
66.    SET @Data = (SELECT * FROM #TempPerson FOR JSON PATH);
67.    SET @JsonData = JSON_MODIFY(@JsonData, '$.data', JSON_QUERY(@Data));
68.    SELECT @JsonData;
69.  
70.    --EXEC SpPersonTsk @JsonData;
71.   END
72.  
73.   IF @Entity = 'Organization'
74.   BEGIN
75.     CREATE TABLE #TempOrganization (OrganizationName VARCHAR(250), DepartmentName VARCHAR(250),[Parent] INT,  Phone VARCHAR(25),Website VARCHAR(1000),
76.             Status VARCHAR(50), Address1 VARCHAR(100), Address2 VARCHAR(100), City VARCHAR(100), State VARCHAR(100), ZipCode VARCHAR(100),
77.             OfficeId INT);
78.     INSERT INTO #TempOrganization (OrganizationName, DepartmentName, Parent,  Phone, Website, Status, Address1, Address2, City, State, ZipCode, OfficeId) VALUES
79.      ('ABC Inc', 'HR', 1, '1234567890', 'www.abc.com', 'Active', '123 Main St', 'Suite 101', 'New York', 'NY', '10001', 1),
80.      ('XYZ Corp', 'Finance', 1, '9876543210', 'www.xyzcorp.com', 'Active', '456 Elm St', 'Apt 202', 'Los Angeles', 'CA', '90001', 2),
81.      ('Acme Corporation', 'Sales', 1, '5551234567', 'www.acmecorp.com', 'Active', '789 Oak Ave', 'Apt 303', 'Chicago', 'IL', '60601', 3),
82.      ('Beta Corp', 'Marketing', 1, '3335557777', 'www.betacorp.com', 'Active', '101 Pine Rd', 'Apt 404', 'Miami', 'FL', '33101', 4),
83.      ('Gamma Industries', 'Operations', 1, '2224446666', 'www.gamma.com', 'Active', '202 Cedar Ln', 'Apt 505', 'Seattle', 'WA', '98100', 5),
84.      ('Delta Ltd', 'IT', 1, '1119998888', 'www.delta.com', 'Active', '303 Maple Dr', 'Apt 606', 'Atlanta', 'GA', '30301', 6),
85.      ('Sigma Solutions', 'Customer Care', 1, '7776663333', 'www.sigma.com', 'Active', '404 Walnut Blvd', 'Apt 707', 'Houston', 'TX', '77001', 7),
86.      ('Epsilon Group', 'Research', 1, '8882224444', 'www.epsilon.com', 'Active', '505 Cherry Ln', 'Apt 808', 'Denver', 'CO', '80201', 8),
87.      ('Zeta Enterprises', 'Development', 1, '9991115555', 'www.zeta.com', 'Active', '606 Birch Rd', 'Apt 909', 'San Francisco', 'CA', '94101', 9),
88.      ('Theta Industries', 'Quality Control', 1, '1239876543', 'www.theta.com', 'Active', '707 Elmwood Ave', 'Apt 1010', 'Boston', 'MA', '21010', 10);
89.  
90.      
91.  
92.      IF EXISTS (SELECT 1 FROM #TempOrganization WHERE TRIM(ISNULL(OrganizationName, ''))= '' OR TRIM(ISNULL(DepartmentName, '')) = '')
93.      RAISERROR('Sorry the OrganizationName or Department Name Cannot be null', 16, 1);
94.  
95.      IF EXISTS(SELECT 1 FROM #TempOrganization AS T WHERE LEN(T.Phone) <> 10)
96.        RAISERROR('Mobile Number should be 10 digits only', 16, 1);
97.  
98.     IF EXISTS (SELECT 1 FROM #TempOrganization AS T WHERE ISNULL(T.Address1, '') = ''  OR ISNULL(T.City, '') = '' OR ISNULL(T.State, '') = '' OR ISNULL(T.ZipCode, '') = '')
99.      RAISERROR('Sorry other address field cannot be empty except Address2', 16, 1);
100.    
101.     IF EXISTS(SELECT 1 FROM #TempOrganization AS T WHERE T.Status NOT IN ('Active', 'Inactive'))
102.      RAISERROR('Sorry the status field can only hold ''active'' or ''inactive'' as a status', 16, 1);
103.  
104.     IF EXISTS ( SELECT 1 FROM #TempOrganization AS T WHERE LEN(T.ZipCode) <> 5)
105.     RAISERROR('Sorry the zip code should be only 5 digits', 16, 1);
106.  
107.     IF EXISTS (SELECT 1 FROM #TempOrganization AS T INNER JOIN Organization AS O ON O.DepartmentName = T.DepartmentName AND O.OrganizationName = T.OrganizationName
108.      AND T.Parent = O.Parent)
109.      RAISERROR('Sorry the organization already exist', 16, 1);
110.  
111.  
112.     SELECT @JsonData = (SELECT 1 AS 'UserPersonId',
113.      'NewCustomer' AS 'Entity' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER);
114.  
115.      SET @Data = (SELECT * FROM #TempOrganization FOR JSON PATH);
116.      SET @JsonData = JSON_MODIFY(@JsonData, '$.data', JSON_QUERY(@Data));
117.  
118.      --EXEC SpOrganizationTsk @JsonData;
119.  
120.      select @JsonData;
121.  
122.    
123.    END
124.  
125.  END TRY
126.  BEGIN CATCH
127.  
128.  
129.   THROW;
130.  END CATCH
131. END
132.  
133.  
134. EXEC SpRikeshImportTsk @Entity = 'Person';