SQL Server – INSERT through Stored Procedure

There is one post in this blog in which we explored how to use the INSERT statement in different ways. Here is another instance of inserting the data through a stored procedure into the table.

Let us create a stored procedure that takes student data and marks as input and returns the student ‘s total and average marks upon execution.

(@iStudentID INT,
@Subject1 INT,
@Subject2 INT,
@Subject3 INT)
DECLARE @iStudent INT, @Total INT, @AVG DECIMAL(7,2)
SELECT @iStudent = @iStudentID
SELECT @Total = @Subject1+@Subject2+@Subject3
SELECT @AVG = @Total/3

SELECT @iStudent, @Total, @AVG

Explanation: The above stored procedure requires three input parameters to execute. Student ID and marks of three subjects. Inside the execution block it summarizes the marks that were provided. Let’s execute this stored procedure to see the result.

EXEC uspMarksAnalyzer 101, 50,60,70

101      180        60.00

Stored Procedure

Now, lets create a table to store the output.
CREATE TABLE Scores(StudentID INT, TotalMarks INT, AvgMarks DECIMAL(7,2))

Insert the data through Stored Procedure
EXEC uspMarksAnalyzer 101, 50,60,70

INSERT through SP

Instead of returning the result on screen, we are storing the information in a table. However, the insert statement can be written inside the stored procedure to avoid such scenario. 

If there is no agile process in place and a long protocol needs to be followed to change an existing stored procedure, such workarounds are required in such scenarios.


  1. No words to Praise you!Thank you very much Mr. Shafi for such a useful and informative blog. You'll be blessed by GOD for such an extraordinarly hard work.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s