Microsoft SQL Server Tutorial Series: Beginner to Expert

Part 11: Stored Procedures and User-Defined Functions in SQL Server

Welcome to Part 11 of our SQL Server series! In this post, you’ll learn how to structure your database logic with Stored Procedures and User-Defined Functions (UDFs).

🧠 What is a Stored Procedure?

A Stored Procedure is a set of SQL statements that can be saved and reused. It allows you to encapsulate logic, improve performance, and enforce security.

✅ Benefits:

Reusability and modularity
Improved performance due to pre-compilation
Security via controlled execution

📘 Basic Syntax Example:

CREATE PROCEDURE GetAllStudents
AS
BEGIN
    SELECT * FROM Students;
END;
GO

🛠️ Execute the Procedure:

EXEC GetAllStudents;

🧩 With Parameters:

CREATE PROCEDURE GetStudentByID
    @StudentID INT
AS
BEGIN
    SELECT * FROM Students WHERE StudentID = @StudentID;
END;
GO

-- Execute it
EXEC GetStudentByID @StudentID = 1;

🧮 What is a User-Defined Function (UDF)?

A User-Defined Function allows you to create reusable SQL logic that returns a single value or a table.

🔹 Scalar Function Example:

CREATE FUNCTION GetStudentAge (@BirthDate DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @BirthDate, GETDATE());
END;
GO

-- Use the function
SELECT dbo.GetStudentAge('2005-03-15') AS Age;

🔹 Table-Valued Function Example:

CREATE FUNCTION GetActiveStudents()
RETURNS TABLE
AS
RETURN (
    SELECT * FROM Students WHERE IsActive = 1
);
GO

-- Use the function
SELECT * FROM dbo.GetActiveStudents();

📊 Comparison Table: Stored Procedure vs Function

Feature	Stored Procedure	User-Defined Function
Return Type	0 or more result sets	Single value or a table
Parameters	Input and Output	Input only
Transaction Control	Yes	No
Use in SELECT	No	Yes
Typical Use	Business logic & data changes	Reusable calculations, filters

🏫 Real-World Example: School System

Use procedures to insert students and functions to calculate GPA or get full names.

CREATE PROCEDURE AddStudent
    @StudentID INT,
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50),
    @BirthDate DATE
AS
BEGIN
    INSERT INTO Students (StudentID, FirstName, LastName, BirthDate, IsActive)
    VALUES (@StudentID, @FirstName, @LastName, @BirthDate, 1);
END;
GO

CREATE FUNCTION FullName(@FirstName VARCHAR(50), @LastName VARCHAR(50))
RETURNS VARCHAR(101)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END;
GO

🧾 SQL Server Cheat Sheet

-- Stored Procedure
CREATE PROCEDURE [Name] AS BEGIN ... END;

-- Scalar Function
CREATE FUNCTION [Name]() RETURNS DataType AS BEGIN RETURN ... END;

-- Table-Valued Function
CREATE FUNCTION [Name]() RETURNS TABLE AS RETURN (SELECT ...);

💡 Best Practices

Tip	Recommendation
Naming Conventions	Use `usp_` prefix for procedures and `fn_` for functions
Security	Grant EXECUTE rights selectively
Performance	Avoid functions in WHERE clause if possible
Readability	Use comments and consistent formatting

✅ Summary

Stored Procedures = reusable blocks of logic
Functions = return data (value or table) for use in queries
Both help you write cleaner, more efficient SQL

🔗 What's Next?

Next up in Part 12, we’ll cover: Views and Triggers in SQL Server.

🔁 Previous Parts:

Share your own stored procedures or UDF tips in the comments below!

← Previous Next →

Learn Python

Menu

Stored Procedures and User-Defined Functions in SQL Server