Stored Procedures

A stored procedure is precompiled T-SQL saved in the database — you call it by name instead of sending raw SQL text. This is exactly what you'll call from C# in the next module.

Creating a stored procedure

CREATE PROCEDURE sp_GetStudentsByLastName
    @LastName NVARCHAR(50)
AS
BEGIN
    SELECT StudentId, FirstName, LastName, Email
    FROM Students
    WHERE LastName = @LastName;
END;

Executing it

EXEC sp_GetStudentsByLastName @LastName = 'Ahmed';

Insert/Update/Delete procedures

CREATE PROCEDURE sp_AddStudent
    @FirstName NVARCHAR(50),
    @LastName  NVARCHAR(50),
    @Email     NVARCHAR(100)
AS
BEGIN
    INSERT INTO Students (FirstName, LastName, Email)
    VALUES (@FirstName, @LastName, @Email);
END;
GO

CREATE PROCEDURE sp_DeleteStudent
    @StudentId INT
AS
BEGIN
    DELETE FROM Students WHERE StudentId = @StudentId;
END;

Why use stored procedures?

• They protect against SQL injection automatically (parameters are never concatenated into the query text).
• Logic lives in one place in the database, callable from any app.
• Can be faster — SQL Server caches the execution plan.