Click here to Skip to main content
15,068,929 members
Please Sign up or sign in to vote.
3.43/5 (3 votes)
I have two tables

emplyoee (first table)
id primary key auto increment
emp_name varchar

student(second table)
id foriegnkey emplyoee.id
st_name varchar

i want to insert multiple student records for a single employe id . My code is attached here , but this use to only one student record update.how can i write stored procedure for this need.
i am new with SQL server and stored procedure , could you please help me.

SQL
create procedure  empst_Sp
@emp_name varchar(50),
@st_name varchar(50)
as
begin
insert into emplyoee (emp_name) values (@emp_name)
insert into student(id,st_name) values(SCOPE_IDENTITY(),@st_name)
end
Posted
Updated 29-Apr-15 18:54pm
v3
Comments
jgakenhe 30-Apr-15 0:51am
   
It is possible to send in multiple records into a stored procedure, but it is hard and this is not what you want to do.

You want to insert 1 record at a time, so if there is a problem you can roll it back (http://en.wikipedia.org/wiki/ACID). My suggestion to you is that if you are calling this from a web page, then when the user clicks submit, you gather the data and For Each record do a single insert. For instance if Student A wants to enroll in 2 classes, when they click submit, your code gets the values and For Each of these classes, it does 1 execution of your store procedure above. Your stored procedure should work just fine.
upendra shahi 30-Apr-15 1:20am
   
nice question..

Hi,

I think you have a data table for the list which you to save along with only one employee id.

You can check this link It is about how you can send datatable to stored procedure.

you can use help from that article and send employee details as simple parameter and student details as specified in the article(as XMl file).
   
Not sure if all the syntax below works with SQL2000.
The solution is to pass a list of Student Names as XML into the Stored Procedure, and update the INSERT statement to insert multiple records.

SQL
DECLARE @students TABLE (st_name VARCHAR(50));
DECLARE @students_xml XML;
INSERT @students
    SELECT 'Michael'
    UNION
    SELECT 'Bob'

-- Convert table into an XML
SET @students_xml =
    (
        SELECT *
        FROM @students AS student
        FOR XML AUTO, ROOT('students')
    );

-- Parse the XML to a table
SELECT
    Tbl.Col.value('@st_name', 'varchar(50)') AS st_name
FROM @students_xml.nodes('//students/student') Tbl(Col)


Also, if you are using SQL 2008 and above, you can use Table-Valued parameter.
   
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900