-
Notifications
You must be signed in to change notification settings - Fork 0
/
StudentDetails_Script.sql
60 lines (54 loc) · 3.69 KB
/
StudentDetails_Script.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
/****** Object: Table [dbo].[StudentDetails] Script Date: 2022-08-03 6:07:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StudentDetails](
[Id] [int] NULL,
[Name] [nvarchar](100) NULL,
[Email] [nvarchar](100) NULL,
[Password] [nvarchar](max) NULL,
[DateofBirth] [date] NULL,
[DateofJoining] [date] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[StudentDetails] ([Id], [Name], [Email], [Password], [DateofBirth], [DateofJoining]) VALUES (1, N'Amit Mohanty', N'amit-mohanty@test.com', N'amit123', CAST(N'1986-07-08' AS Date), CAST(N'2022-08-01' AS Date))
GO
INSERT [dbo].[StudentDetails] ([Id], [Name], [Email], [Password], [DateofBirth], [DateofJoining]) VALUES (2, N'Abinash Hota', N'abhinash.hota@test.com', N'test123', CAST(N'1972-05-13' AS Date), CAST(N'2010-05-10' AS Date))
GO
/****** Object: StoredProcedure [dbo].[GetStudentDetails] Script Date: 2022-08-03 6:07:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetStudentDetails]
(
@Id INT = NULL, @Name NVARCHAR(100) = NULL, @Email NVARCHAR(100) = NULL,
@Password NVARCHAR(MAX) = NULL, @DateofBirth DATE = NULL, @DateofJoining DATE = NULL,
@Action NVARCHAR(20)
)
AS
BEGIN
IF @Action = 'StudentDetails'
BEGIN
SELECT * FROM StudentDetails
END
IF @Action = 'StudentData'
BEGIN
SELECT * FROM StudentDetails WHERE id = @Id
END
IF @Action = 'AddStudent'
BEGIN
INSERT INTO StudentDetails VALUES (@Id, @Name, @Email, @Password, @DateofBirth, @DateofJoining)
END
IF @Action = 'UpdateStudent'
BEGIN
UPDATE StudentDetails SET Name = @Name, Email = @Email, Password = @Password, DateofBirth = @DateofBirth,
DateofJoining = @DateofJoining WHERE Id = @Id
END
IF @Action = 'DeleteStudent'
BEGIN
DELETE FROM StudentDetails WHERE id = @Id
END
END
GO