Stored Procedures

Aside from functions to encapsulate our code, the MS SQL Server allows us to create stored procedures, which are a named set of T-SQL statements that can be executed and reused an arbitrary number of times.

Creation

This is the syntax for declaring a stored procedure:

CREATE PROCEDURE Schema.ProcedureName
AS
BEGIN
    [Procedure body]
END

Based on this, we can see that the syntax is similar to the function declaration.

Execution

To execute a stored procedure, we use the EXEC keyword (or EXECUTE), followed by the stored procedure name:

EXEC Schema.StoredProcedureName

Example

Let’s consider the FinalExamScores table.