-
Notifications
You must be signed in to change notification settings - Fork 1
/
sqlScripts.txt
36 lines (36 loc) · 935 Bytes
/
sqlScripts.txt
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
------TABLE DEFENITION IN SQL
CREATE TABLE TestEmployee(Id INT IDENTITY(1,1) PRIMARY KEY, @Name VARCHAR(100), @Age INT, @Active INT);
------STORED PROCEDURES IN SQL
--proc to add employee
CREATE PROC usp_AddEmployee(@Name VARCHAR(100), @Age INT, @Active INT)
AS
BEGIN
INSERT INTO TestEmployee(Name, Age, Active)
VALUES(@Name, @Age, @Active);
END
--proc to select all employees
CREATE PROC usp_GetAllEmployees
AS
BEGIN
SELECT * FROM TestEmployee;
END
--proc to select a single employee by id
CREATE PROC usp_GetEmployeeById(@Id INT)
AS
BEGIN
SELECT * FROM TestEmployee WHERE Id = @Id;
END
--proc to update employee
CREATE PROC usp_UpdateEmployee(@Id INT, @Name VARCHAR(100), @Age INT, @Active INT)
AS
BEGIN
UPDATE TestEmployee
SET Name = @Name, Age = @Age, Active = @Active
WHERE Id = @Id;
END
--proc to delete employee
CREATE PROC usp_DeleteEmployee(@Id INT)
AS
BEGIN
DELETE FROM TestEmployee where Id = @Id;
END