You question is not clear. I am assuming you have more than one Academic Record that you want to insert into to your database through a stored procedure.
The only reason I can think of you wanting to insert the records by passing it as values into the stored procedure is because the stored procedure inserts data into multiple tables.
Here is a sample approach.
Lets consider an example where the requirement is to insert multiple users into the database.
This is the user table
CREATE TABLE tblUser
(
UserID INT IDENTITY(1,1),
UserName VARCHAR(50),
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(50)
)
This is the stored procedure that inserts data into user table
CREATE PROCEDURE usp_CreateUser
@UserName VARCHAR(50),
@FirstName VARCHAR(50),
@LastName VARCHAR(50),
@Email VARCHAR(50)
AS
BEGIN
INSERT INTO tblUser
(
UserName,
FirstName,
LastName,
Email
)
VALUES
(
@UserName,
@FirstName,
@LastName,
@Email
)
END
First we create a temp table that holds all the users we want to insert as shown below.
DECLARE @TempUserList TABLE
(
ID INT IDENTITY(1,1),
UserName VARCHAR(50),
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(50)
)
INSERT INTO @TempUserList
SELECT 'chuckynoris', 'Chuck', 'Norris', 'chuckynoris@somedomain.com' UNION ALL
SELECT 'CPike', 'Christopher', 'Pike', 'CPike@somedomain.com'
Then we read each row from temp table and pass it to the stored procedure in a loop as shown below
DECLARE @Count INT --Used to get the present Row ID
DECLARE @MaxCount INT --Used to hold the maximum number of rows in the temp table
SELECT @Count = 1, @MaxCount = COUNT(*) FROM @TempUserList
--These variables will hold the values that will be passed into the stored procedure.
DECLARE @UserName VARCHAR(50),
@FirstName VARCHAR(50),
@LastName VARCHAR(50),
@Email VARCHAR(50)
WHILE @Count <= @MaxCount
BEGIN
SELECT @UserName = UserName, @FirstName = FirstName, @LastName = LastName, @Email = Email FROM @TempUserList WHERE ID = @Count
EXEC usp_CreateUser @UserName, @FirstName, @LastName, @Email
SET @Count = @Count + 1
END
SELECT * FROM tblUser
Hope this helps.