Library-Management-System/Library_Management_System(Some Stored Procedures).sql at main · MakCoder-2004/Library-Management-System · 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
CREATE PROC SP_addStaff
@name varchar(100),
@ssn varchar(14),
@address varchar(200),
@hire date,
@phone1 varchar(20),
@phone2 varchar(20),
@email varchar(30),
@birth date,
@work varchar(30)
as
INSERT INTO Staff
VALUES(@name, @ssn, @address, @phone1, @phone2, @birth, @work, @hire, @email)


CREATE PROCEDURE SP_authenticate
    @User VARCHAR(20),
    @Pass VARCHAR(20)
AS
BEGIN
    SELECT COUNT(*)
    FROM dbo.User_Pass
    WHERE Username = @User AND Password = @Pass;
END

CREATE PROC SP_addMember
@name varchar(100),
@ssn varchar(14),
@address varchar(200),
@phone1 varchar(20),
@phone2 varchar(20),
@email varchar(100),
@birth date,
@qr varchar(50),
@membership bit,
@borrow bit
as
INSERT INTO Members
VALUES(@name, @ssn, @address, @phone1, @phone2, @email, @birth, @qr, @membership, @borrow)


CREATE PROC SP_editStaff
@id int,
@name varchar(100),
@ssn varchar(14),
@address varchar(200),
@hire date,
@phone1 varchar(20),
@phone2 varchar(20),
@email varchar(30),
@birth date,
@work varchar(30)
as
UPDATE Staff SET name = @name, ssn = @ssn, address = @address, HireDate = @hire, phone1 = @phone1, phone2 = @phone2, email = @email, BirthDate = @birth, WorkType = @work
WHERE id = @id


CREATE PROC SP_editMember
@id int,
@name varchar(100),
@ssn varchar(14),
@address varchar(200),
@phone1 varchar(20),
@phone2 varchar(20),
@email varchar(100),
@birth date,
@membership bit,
@borrow bit
as
UPDATE Members SET name = @name, ssn = @ssn, address = @address, phone1 = @phone1, phone2 = @phone2, email = @email, BirthDate = @birth, MembershipStatus = @membership, BorrowState = @borrow
WHERE id = @id


CREATE PROC SP_searchStaff
@id int
as
SELECT * FROM Staff
WHERE id = @id

CREATE PROC SP_searchMember
@id int
as
SELECT * FROM Members
WHERE id = @id


CREATE PROC SP_delStaff
@id int
as
DELETE FROM Staff
WHERE id = @id


CREATE PROC SP_delMember
@id int
as
DELETE FROM Members
WHERE id = @id


CREATE PROC SP_authenticate
@user VARCHAR(20),
@pass VARCHAR(20)
AS
SELECT COUNT(id) FROM User_Pass
WHERE Username = @user AND Password = @pass

EXEC SP_authenticate @User = 'admin', @Pass = 'admin123';