Beyond the Basics with Modeling and Inheritance - Entity Framework 6 Recipes - page 201

7. Create the stored procedures in Listing 6-20. We will map these procedures to the Insert,

Update, and Delete actions for the Principal and Instructor entities.

Listing 6-20. Stored Procedures for the Insert, Update, and Delete Actions for the Instructor and
Principal Entities

create procedure [chapter6].[InsertInstructor]
(@Name varchar(50), @Salary decimal)
as
begin
declare @staffid int
insert into Chapter6.Staff(Name) values (@Name)
set @staffid = SCOPE_IDENTITY()
insert into Chapter6.Instructor(Salary,StaffId) values (@Salary,@staffid)
select @staffid as StaffId,SCOPE_IDENTITY() as InstructorId
end
go

create procedure [chapter6].[UpdateInstructor]
(@Name varchar(50), @Salary decimal, @StaffId int, @InstructorId int)
as
begin
update Chapter6.Staff set Name = @Name where StaffId = @StaffId
update Chapter6.Instructor set Salary = @Salary where InstructorId = @InstructorId
end
go

create procedure [chapter6].[DeleteInstructor]
(@StaffId int)
as
begin
delete Chapter6.Staff where StaffId = @StaffId
delete Chapter6.Instructor where StaffId = @StaffId
end
go

create procedure [Chapter6].[InsertPrincipal]
(@Name varchar(50),@Salary decimal,@Bonus decimal)
as
begin
declare @staffid int
insert into Chapter6.Staff(Name) values (@Name)
set @staffid = SCOPE_IDENTITY()
insert into Chapter6.Principal(Salary,Bonus,StaffId) values
(@Salary,@Bonus,@staffid)
select @staffid as StaffId, SCOPE_IDENTITY() as PrincipalId
end
go

create procedure [Chapter6].[UpdatePrincipal]
(@Name varchar(50),@Salary decimal, @Bonus decimal, @StaffId int, @PrincipalId int)
as