EmployeesOps/StoredProcedures.sql at master · crisvargas03/EmployeesOps · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
USE employeesDb
go
-- GetAllEmployees --
CREATE PROCEDURE [dbo].[GetAllEmployees]
AS
BEGIN
SET NOCOUNT ON;
    SELECT *
    FROM [Employees]
    WHERE [IsDeleted] = 0
END;
go

-- GetById --
CREATE PROCEDURE [dbo].[GetEmployeeById]
    @EmployeeId uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;
    SELECT *
    FROM [Employees]
    WHERE [Id] = @EmployeeId
    AND [IsDeleted] = 0
END;
go

-- InsertEmployee --
CREATE PROCEDURE [dbo].[InsertEmployee]
    @Id uniqueidentifier,
    @Names nvarchar(80),
    @LastNames nvarchar(80),
    @IdentificationNumber nvarchar(20),
    @BirthDate date,
    @EntryDate date,
    @Salary float,
    @IdentificationTypeId int,
    @DepartmentId int,
    @CreatedBy nvarchar(50),
    @CreationDate datetime
AS
BEGIN
    INSERT INTO [employeesDb].[dbo].[Employees] (
        [Id],
        [Names],
        [LastNames],
        [IdentificationNumber],
        [BirthDate],
        [EntryDate],
        [Salary],
        [IdentificationTypeId],
        [DepartmentId],
        [CreatedBy],
        [CreationDate],
        [IsDeleted],
        [ModificationBy],
        [ModificationDate]
    )
    VALUES (
        @Id,
        @Names,
        @LastNames,
        @IdentificationNumber,
        @BirthDate,
        @EntryDate,
        @Salary,
        @IdentificationTypeId,
        @DepartmentId,
        @CreatedBy,
        @CreationDate,
        0,
        NULL,
        NULL
    );
END;
go

-- UpdateEmployee --
CREATE PROCEDURE [dbo].[UpdateEmployee]
    @Id uniqueidentifier,
    @Names nvarchar(50),
    @LastNames nvarchar(50),
    @IdentificationNumber nvarchar(20),
    @BirthDate date,
    @EntryDate date,
    @Salary float,
    @IdentificationTypeId int,
    @DepartmentId int,
    @ModificationBy nvarchar(50),
    @ModificationDate datetime
AS
BEGIN
    UPDATE [employeesDb].[dbo].[Employees]
    SET
        [Names] = @Names,
        [LastNames] = @LastNames,
        [IdentificationNumber] = @IdentificationNumber,
        [BirthDate] = @BirthDate,
        [EntryDate] = @EntryDate,
        [Salary] = @Salary,
        [IdentificationTypeId] = @IdentificationTypeId,
        [DepartmentId] = @DepartmentId,
        [ModificationBy] = @ModificationBy,
        [ModificationDate] = @ModificationDate
    WHERE [Id] = @Id;
END;
go

--DeleteEmployee--
CREATE PROCEDURE [dbo].[DeleteEmployee]
    @EmployeeId uniqueidentifier
AS
BEGIN
    UPDATE [employeesDb].[dbo].[Employees]
    SET
        [IsDeleted] = 1
    WHERE [Id] = @EmployeeId
END;