UPDATE

if exists(select name from sysobjects where name ='pro_update' )

drop procedure pro_update

go

create procedure pro_update( @MasV char(6),@hoten nvarchar(30) , @DIACHI nvarchar(50),@malop char(6))

AS

begin

if exists( select MASV from SINHVIEN where MASV= @MASV )

begin

Alter table Sinhvien nocheck  Constraint all

UPDATE SINHVIEN

SET HOTEN = @hoten,DIACHI = @DIACHI,MALOP =@malop

WHERE MASV = @MasV

Alter table Sinhvien check  Constraint all

return 1

end

Else

return 0

end

Execute pro_update 'HV03','NGUYEN VAN GIAU',N'HÀ NỘI','CNTTK2'

INSERT

If Exists ( Select name From sys.objects Where name like 'Prp_cau1')

Drop Procedure Prp_cau1

Go

Create Proc Prp_cau1 ( @masv char(6), @hoten nvarchar (30),

                                       @diachi nvarchar (50),@malop char(6)

                                   )

As

--Nếu bên table chính đã có dữ liệu

If Exists( Select malop From LOP where MALOP =@malop)

Begin

Insert into sinhvien ( MASV, HOTEN, diachi,  MALOP)

 Values (@masv, @hoten,@diachi,@malop )

Print' Đã chèn thành công'

End

Else

Print N'table lop chưa có dữ liệu'

Return 0

Go

--Cho thực  thi Procedure vừa tạo

 Exec Prp_cau1 'HV03','HFSSKS','SFDF','CNTTK2'

DELETE

if exists(select name from sysobjects where name ='pro_DELETE' )

drop procedure pro_DELETE

go

create procedure pro_DELETE ( @MasV char(6))

AS

begin

if exists( select MASV from SINHVIEN where MASV= @MASV )

begin

Alter table Sinhvien nocheck  Constraint all

DELETE FROM SINHVIEN

WHERE MASV = @MasV

Alter table Sinhvien check  Constraint all

return 1

end

Else

return 0

end

Execute pro_DELETE 'HV03'