Insert Stored Procedure for One to many relationship SQL

Question

Asp.net Form

Student Name
Student Address
English (marks)
Maths (marks)
Science (marks)

For the above form I have created the following table structure

Create Table Student
(
  sId int primary key identity(100,1),
  sName varchar(50),
  sAddress varchar(100)
)   

Create Table Subjects
(
  subId int primary key,
  subName varchar(100)
)

Create Table Marks
(
  mid int primary key identity(1,1),
  sId int foreign key references Student(sId) ,
  subId int foreign key references Subjects(subId),
   MarkPercent int
)      

Create Procedure uspAddStudentDetails
(
  @sName varchar(50),
  @sAddress varchar(100),
  @English int,
  @Maths int,
  @Science int
)
AS
Begin
  Insert into Students (sName, sAddress) values (@sName, @sAddress)
End

I would like to create a stored procedure to insert once into the Student table then insert multiple details into the Marks table as mentioned below. I tried to some extent in the above SP.

Subjects

1000  English
2000  Maths
3000  Science

Student

100  #10,Madison Rd.

Marks

1  100  1000 90
2  100  2000 76
3  100  3000 80

Answer 1

I think the below Procedure Suits your requirements:

First Insert this Value

insert into subjects values(1000,'English') 

insert into subjects values(2000,'Maths')

insert into subjects values(3000,'Science')

Use this Procedure:

Create Procedure uspAddStudentDetails
(
  @sName varchar(50),
  @sAddress varchar(100),
  @English int,
  @Maths int,
  @Science int
)
AS
Begin

declare @sid int
declare @sqlinsert varchar(max)


  Insert into Student (sName, sAddress) values (@sName, @sAddress)


  select @sid=sid from Student where sName=@sName



 if @English is not null
 begin 
   select @sqlinsert=' insert into Marks(sId,subId,MarkPercent)values ('+convert(varchar(20),@sid)+',1000,'+convert(varchar(20),@English)+')'
   exec (@sqlinsert)
 end

 if @Maths is not null
 begin 

   select @sqlinsert=' insert into Marks(sId,subId,MarkPercent)values ('+convert(varchar(20),@sid)+',2000,'+convert(varchar(20),@maths)+')'
    exec (@sqlinsert)
 end

 if @Science is not null
 begin

   select @sqlinsert=' insert into Marks(sId,subId,MarkPercent)values ('+convert(varchar(20),@sid)+',3000,'+convert(varchar(20),@Science)+')'
    exec (@sqlinsert)
 end 


End

And Execute the Procedure:

exec uspAddStudentDetails 'kavin','no:10,Chennai',169,183,150

Output in Student table:

sId sName   sAddress
100 kavin   no:10,Chennai

Marks Table:

mid sId subId   MarkPercent
1   100 1000    169
2   100 2000    183
3   100 3000    150