ajax/SQLQuery.sql at main · ichibulup/ajax · 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
CREATE DATABASE MVCDB;

Go

USE MVCDB;

GO

CREATE TABLE dbo.Employee (
	EmployeeID INT identity NOT NULL
	,Name NVARCHAR(50)
	,Age INT
	,STATE NVARCHAR(50)
	,Country NVARCHAR(50)
	,CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID)
	)

GO

--Select Employees
CREATE PROCEDURE SelectEmployee
AS
BEGIN
	SELECT *
	FROM Employee;
END

GO

--Insert and Update Employee
CREATE PROCEDURE InsertUpdateEmployee (
	@Id INTEGER
	,@Name NVARCHAR(50)
	,@Age INTEGER
	,@State NVARCHAR(50)
	,@Country NVARCHAR(50)
	,@Action VARCHAR(10)
	)
AS
BEGIN
	IF @Action = 'Insert'
	BEGIN
		INSERT INTO Employee (
			Name
			,Age
			,[State]
			,Country
			)
		VALUES (
			@Name
			,@Age
			,@State
			,@Country
			);
	END

	IF @Action = 'Update'
	BEGIN
		UPDATE Employee
		SET Name = @Name
			,Age = @Age
			,[State] = @State
			,Country = @Country
		WHERE EmployeeID = @Id;
	END
END

GO

--Delete Employee
CREATE PROCEDURE DeleteEmployee (@Id INTEGER)
AS
BEGIN
	DELETE Employee
	WHERE EmployeeID = @Id;
END