--------------Bai 2----------------
create proc tinhNgay
	@month_year varchar(7)
as
	declare @year int
	declare @month int
	declare @munberOfDay int

	set @year = CAST(RIGHT(@month_year, 4) as int)
	set @month = CAST(LEFT(@month_year, 2) as int)
	set @munberOfDay =
	case
		when @month in (1,3,5,7,8,10,12) then 31
		when @month in (4,6,9,11) then 30
		when (@month in (2)) and (@year%4=0) then 29
		else 28
	end
	return @munberOfDay
 -----------------------------

declare @date char(7)
execute @date = tinhNgay @month_year = '02/2017'
select @date

------------Bai 3------------------
go
create proc spThemBang
as
	IF exists ( select * from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Test')
	BEGIN
		drop table Test
	END

	create table Test (
		Col1 int primary key,
		Col2 char(3) not null
	)

	declare @i int
	set @i = 0

	while (@i < 26)
	begin
		insert into Test (Col1, Col2) values (@i, CHAR(@i + 97));
		set @i += 1;
	end


exec spThemBang
select* from Test

IF OBJECT_ID('spThemBang') IS NOT NULL DROP PROC spThemBang

-------------Bai 4-------------
go
create table nhanvien (
	manv int not null primary key,
	hoten varchar(30) not null,
	diachi varchar(30) not null
)
go
create proc insert_nhanvien
	@ma_nv int ,
	@name varchar(30),
	@diachi varchar(30)
as
	insert into nhanvien values(@ma_nv, @name, @diachi)

go
create proc update_nhanvien
	@ma_nv int,
	@diachi_dung varchar(30)
as
	update nhanvien
	set diachi = @diachi_dung
	where manv = @ma_nv

exec insert_nhanvien @ma_nv = 1, @name = 'Huy Dep Trai', @diachi = 'Cau Dau Sau'
exec update_nhanvien @ma_nv = 1, @diachi_dung = '01 Ly Tu Trong, NK–TPCT'
select * from nhanvien


-------------Bai 5-----------
go
use NorthWind
go
create proc moneyFrom_order
	@id_order char(5)
as
	select SUM(UnitPrice*Quantity - UnitPrice*Quantity*Discount) as Price
	from [Order Details]
	where OrderID = @id_order

exec moneyFrom_order '10248'