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.

CREATE PROCEDURE uspMarksAnalyzer
(@iStudentID INT,
@Subject1 INT,
@Subject2 INT,
@Subject3 INT)
AS
BEGIN
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
END

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

Result:
101      180        60.00



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
INSERT INTO Scores
EXEC uspMarksAnalyzer 101, 50,60,70



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.