-
Notifications
You must be signed in to change notification settings - Fork 0
/
Positions.sql
83 lines (67 loc) · 1.44 KB
/
Positions.sql
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
USE RAC_SARAYAT
GO
CREATE OR ALTER PROC usp_Positions_Add
@PositionName NVARCHAR(100),
@PositionDescription NVARCHAR(500),
@HexCode NVARCHAR(2) OUTPUT,
@HexMsg NVARCHAR(100) OUTPUT
AS
BEGIN
IF EXISTS(SELECT * FROM Positions WHERE PositionName = @PositionName)
BEGIN
SET @HexCode ='01'
SET @HexMsg = 'Position Already Exists'
RETURN -1
END
INSERT INTO Positions
(
PositionName,
PositionDescription
)
VALUES
(
@PositionName,
@PositionDescription
)
IF NOT EXISTS(SELECT * FROM Positions WHERE PositionName = @PositionName)
BEGIN
SET @HexCode ='01'
SET @HexMsg = 'Failed To Add Position'
RETURN -1
END
SET @HexCode = '00'
SET @HexMsg = 'Position Added Successfully!'
END
GO
CREATE OR ALTER PROC usp_Positions_GetAll
AS
BEGIN
SELECT * FROM Positions
END
GO
CREATE OR ALTER PROC usp_Positions_EditDescription
@PositionID INT,
@PositionDescription NVARCHAR(500),
@HexCode NVARCHAR(2) OUTPUT,
@HexMsg NVARCHAR(100) OUTPUT
AS
BEGIN
IF NOT EXISTS(SELECT * FROM Positions WHERE ID = @PositionID)
BEGIN
SET @HexCode = '01'
SET @HexMsg = 'Position Does Not Exist, Check Position ID'
RETURN -1
END
UPDATE Positions
SET PositionDescription = @PositionDescription
WHERE ID = @PositionID
IF NOT EXISTS(SELECT * FROM Positions WHERE ID = @PositionID AND PositionDescription = @PositionDescription)
BEGIN
SET @HexCode = '01'
SET @HexMsg = 'Position Update Failed!'
RETURN -1
END
SET @HexCode = '00'
SET @HexMsg = 'Description Updated Successfully!'
END
GO