26. Stored Procedures & Functions -- Reusable SQL Code

Level: Advanced beginner

ℹ️ What You'll Learn

• Stored procedure syntax and creation
• Input and output parameters
• EXECUTE command to call procedures
• Scalar functions (single value return)
• Table-valued functions (return table)
• Error handling (TRY-CATCH)
• Transaction control (BEGIN-COMMIT)
• Common procedure/function mistakes
• Best practices

Queries written once, used many times. Procedures and functions reuse code. This article teaches creating reusable SQL logic.

Why Procedures & Functions Matter for Backend Developers

APIs need encapsulated logic:

Backend needs: "Insert exam result with validation
               (marks in range, student exists, no duplicate entry)"
    v
Without procedure: Validation logic in backend code
WITH procedure:    Business logic in database
    v
Single source of truth. Consistent across all clients

Procedures enforce rules at database layer.

Stored Procedure Syntax

CREATE PROCEDURE ProcedureName
    @InputParam1 INT,
    @InputParam2 VARCHAR(100),
    @OutputParam INT OUTPUT
AS
BEGIN
    -- SQL logic
    SELECT @OutputParam = COUNT(*) FROM Table;
END;

-- Call:
DECLARE @Result INT;
EXECUTE ProcedureName 
    @InputParam1 = 10,
    @InputParam2 = 'Value',
    @OutputParam = @Result OUTPUT;
SELECT @Result AS Result;

Parts:

• @Param: Parameter (starts with @)
• INPUT: Pass value in
• OUTPUT: Get value out
• EXECUTE: Call procedure

Example 1: Insert with Validation

Add exam result with checks:

CREATE PROCEDURE InsertExamResult
    @StudentId INT,
    @ExamId INT,
    @MarksObtained DECIMAL(5,2),
    @IsAbsent BIT,
    @IsSuccess BIT OUTPUT,
    @ErrorMessage VARCHAR(500) OUTPUT
AS
BEGIN
    SET @IsSuccess = 0;
    SET @ErrorMessage = '';
    
    -- Validate student exists
    IF NOT EXISTS (SELECT 1 FROM Student WHERE StudentId = @StudentId)
    BEGIN
        SET @ErrorMessage = 'Student not found';
        RETURN;
    END;
    
    -- Validate exam exists
    IF NOT EXISTS (SELECT 1 FROM Exam WHERE ExamId = @ExamId)
    BEGIN
        SET @ErrorMessage = 'Exam not found';
        RETURN;
    END;
    
    -- Validate marks in range
    DECLARE @MaxMarks DECIMAL(5,2);
    SELECT @MaxMarks = MaxMarks FROM Exam WHERE ExamId = @ExamId;
    
    IF @MarksObtained < 0 OR @MarksObtained > @MaxMarks
    BEGIN
        SET @ErrorMessage = 'Marks out of range';
        RETURN;
    END;
    
    -- Check duplicate
    IF EXISTS (SELECT 1 FROM ExamResult WHERE StudentId = @StudentId AND ExamId = @ExamId)
    BEGIN
        SET @ErrorMessage = 'Exam result already exists';
        RETURN;
    END;
    
    -- Insert
    INSERT INTO ExamResult (StudentId, ExamId, MarksObtained, IsAbsent)
    VALUES (@StudentId, @ExamId, @MarksObtained, @IsAbsent);
    
    SET @IsSuccess = 1;
    SET @ErrorMessage = 'Success';
END;

-- Call:
DECLARE @Success BIT, @Message VARCHAR(500);
EXECUTE InsertExamResult 
    @StudentId = 1,
    @ExamId = 5,
    @MarksObtained = 92.5,
    @IsAbsent = 0,
    @IsSuccess = @Success OUTPUT,
    @ErrorMessage = @Message OUTPUT;
SELECT @Success AS Success, @Message AS Message;

Result:

Success | Message
1       | Success

Validates student, exam, marks range, no duplicate. Returns success and message.

Example 2: Scalar Function (return single value)

Calculate student age:

CREATE FUNCTION CalculateStudentAge (@DateOfBirth DATE)
RETURNS INT
AS
BEGIN
    DECLARE @Age INT;
    SET @Age = DATEDIFF(YEAR, @DateOfBirth, GETDATE()) - 
               CASE 
                   WHEN MONTH(@DateOfBirth) > MONTH(GETDATE())
                        OR (MONTH(@DateOfBirth) = MONTH(GETDATE()) 
                            AND DAY(@DateOfBirth) > DAY(GETDATE()))
                   THEN 1
                   ELSE 0
               END;
    RETURN @Age;
END;

-- Use in query:
SELECT 
    Name,
    DateOfBirth,
    dbo.CalculateStudentAge(DateOfBirth) AS Age
FROM Student;

Result (ages):

Name        | DateOfBirth | Age
Ravi Kumar  | 2008-05-15  | 16
Priya Sharma| 2007-11-20  | 18

Function returns scalar (single INT). Call with dbo.FunctionName.

Example 3: Table-Valued Function (return result set)

Get top N students by marks:

CREATE FUNCTION TopStudentsByMarks (@TopN INT)
RETURNS TABLE
AS
RETURN
(
    SELECT TOP (@TopN)
        s.Name AS Student,
        s.ClassName,
        r.MarksObtained,
        RANK() OVER (PARTITION BY s.ClassName ORDER BY r.MarksObtained DESC) AS ClassRank
    FROM Student s
    INNER JOIN ExamResult r ON s.StudentId = r.StudentId
    ORDER BY s.ClassName, r.MarksObtained DESC
);

-- Use in query:
SELECT * FROM dbo.TopStudentsByMarks(3);

Result (top 3):

Student     | ClassName | MarksObtained | ClassRank
Ravi Kumar  | 10-A      | 92            | 1
Priya Sharma| 10-A      | 85            | 2
Sneha Patel | 10-A      | 78            | 3

Table function returns entire result set.

Example 4: Procedure with Try-Catch (Error Handling)

Insert student with error handling:

CREATE PROCEDURE InsertStudent
    @Name NVARCHAR(100),
    @RollNumber NVARCHAR(20),
    @ClassName NVARCHAR(10),
    @StudentId INT OUTPUT,
    @IsSuccess BIT OUTPUT,
    @ErrorMessage VARCHAR(500) OUTPUT
AS
BEGIN
    SET @IsSuccess = 0;
    SET @ErrorMessage = '';
    
    BEGIN TRY
        -- Check duplicate roll number
        IF EXISTS (SELECT 1 FROM Student WHERE RollNumber = @RollNumber AND ClassName = @ClassName)
        BEGIN
            THROW 50001, 'Duplicate roll number in same class', 1;
        END;
        
        -- Insert
        INSERT INTO Student (Name, RollNumber, ClassName, Status)
        VALUES (@Name, @RollNumber, @ClassName, 'Active');
        
        SET @StudentId = SCOPE_IDENTITY();  -- Get inserted ID
        SET @IsSuccess = 1;
        SET @ErrorMessage = 'Student added successfully';
    END TRY
    BEGIN CATCH
        SET @IsSuccess = 0;
        SET @ErrorMessage = ERROR_MESSAGE();
    END CATCH
END;

-- Call:
DECLARE @StuId INT, @Success BIT, @Msg VARCHAR(500);
EXECUTE InsertStudent 
    @Name = 'Ravi Kumar',
    @RollNumber = '001',
    @ClassName = '10-A',
    @StudentId = @StuId OUTPUT,
    @IsSuccess = @Success OUTPUT,
    @ErrorMessage = @Msg OUTPUT;
SELECT @StuId AS NewStudentId, @Success AS Success, @Msg AS Message;

Result:

NewStudentId | Success | Message
42           | 1       | Student added successfully

TRY-CATCH: Catches errors, returns message. SCOPE_IDENTITY(): Get inserted ID.

Example 5: Procedure with Transaction

Batch insert with rollback on error:

CREATE PROCEDURE InsertMultipleExamResults
    @ExamId INT,
    @ResultsXML XML
AS
BEGIN
    BEGIN TRANSACTION;
    BEGIN TRY
        -- Parse XML into table
        INSERT INTO ExamResult (StudentId, ExamId, MarksObtained, IsAbsent)
        SELECT 
            T.value('@StudentId', 'INT'),
            @ExamId,
            T.value('@Marks', 'DECIMAL(5,2)'),
            T.value('@IsAbsent', 'BIT')
        FROM @ResultsXML.nodes('/Results/Result') AS X(T);
        
        COMMIT TRANSACTION;
        SELECT 'All results inserted successfully' AS Result;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SELECT 'Insert failed. Transaction rolled back.' AS Result;
    END CATCH
END;

Transaction: BEGIN, commit on success, ROLLBACK on error. All-or-nothing insert.

Example 6: Function for Percentage Calculation

Reusable percentage function:

CREATE FUNCTION CalculatePercentage (@Obtained DECIMAL(5,2), @Total DECIMAL(5,2))
RETURNS DECIMAL(5,2)
AS
BEGIN
    IF @Total = 0 RETURN 0;
    RETURN ROUND(@Obtained * 100.0 / @Total, 2);
END;

-- Use:
SELECT 
    s.Name,
    r.MarksObtained,
    e.MaxMarks,
    dbo.CalculatePercentage(r.MarksObtained, e.MaxMarks) AS Percentage
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
INNER JOIN Exam e ON r.ExamId = e.ExamId;

Result:

Name        | MarksObtained | MaxMarks | Percentage
Ravi Kumar  | 92            | 100      | 92.00
Priya Sharma| 85            | 100      | 85.00

Function encapsulates calculation. Reuse everywhere.

---

Common Procedure/Function Mistakes

Mistake 1: Procedure without BEGIN-END

Wrong:

CREATE PROCEDURE GetStudent @Id INT
SELECT * FROM Student WHERE StudentId = @Id;
-- Works but unclear scope

Fix: Use BEGIN-END:

CREATE PROCEDURE GetStudent @Id INT
AS
BEGIN
    SELECT * FROM Student WHERE StudentId = @Id;
END;

Mistake 2: Output parameter not initialized

Wrong:

CREATE PROCEDURE CheckStudent @Id INT, @Exists BIT OUTPUT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM Student WHERE StudentId = @Id)
        SET @Exists = 1;
    -- If not exists: @Exists NULL (not 0)
END;

Fix: Initialize OUTPUT:

SET @Exists = 0;  -- Default before logic
IF EXISTS (...)
    SET @Exists = 1;

Mistake 3: Function with multiple RETURN statements

Wrong:

CREATE FUNCTION GetAverage (@ClassId INT)
RETURNS DECIMAL(5,2)
AS
BEGIN
    IF @ClassId = 1 RETURN 85.5;
    IF @ClassId = 2 RETURN 80.0;
    RETURN 0;
END;
-- Multiple RETURNs: confusing

Fix: Single RETURN at end:

CREATE FUNCTION GetAverage (@ClassId INT)
RETURNS DECIMAL(5,2)
AS
BEGIN
    DECLARE @Avg DECIMAL(5,2) = 0;
    SELECT @Avg = AVG(MarksObtained)
    FROM ExamResult er
    INNER JOIN Exam e ON er.ExamId = e.ExamId
    INNER JOIN Subject s ON e.SubjectId = s.SubjectId
    WHERE s.ClassName = (SELECT ClassName FROM Class WHERE ClassId = @ClassId);
    RETURN @Avg;
END;

Mistake 4: Not handling NULL parameters

Wrong:

CREATE PROCEDURE InsertClass @ClassName VARCHAR(10)
AS
BEGIN
    INSERT INTO Class (ClassName) VALUES (@ClassName);
    -- If @ClassName NULL: inserts NULL (invalid)
END;

Fix: Validate:

IF @ClassName IS NULL OR @ClassName = ''
BEGIN
    THROW 50001, 'Class name required', 1;
END;
INSERT INTO Class (ClassName) VALUES (@ClassName);

Mistake 5: No error handling (THROW on error)

Wrong:

CREATE PROCEDURE AddExamResult
AS
BEGIN
    -- No TRY-CATCH
    INSERT INTO ExamResult VALUES (...);
    -- If error: unhandled, caller doesn't know
END;

Fix: Add error handling:

BEGIN TRY
    INSERT INTO ExamResult VALUES (...);
END TRY
BEGIN CATCH
    THROW;  -- Re-throw to caller
END CATCH

---

Best Practices for Procedures/Functions

1. Use procedures for business logic -- Validation, multiple steps
2. Use functions for reusable calculations -- Percentage, age, averages
3. Always initialize OUTPUT parameters -- Set defaults first
4. Handle NULL inputs -- Validate before use
5. Add TRY-CATCH for error handling -- Know what failed
6. Use transactions for multi-step inserts -- All-or-nothing
7. Document parameters -- What input expected, what output

---

🎯 Q1: What's the difference between PROCEDURE and FUNCTION?

PROCEDURE: Actions (INSERT, UPDATE, DELETE).

CREATE PROCEDURE InsertStudent @Name NVARCHAR(100) AS
BEGIN
    INSERT INTO Student (Name) VALUES (@Name);
END;

FUNCTION: Calculations (return value).

CREATE FUNCTION GetAge (@DateOfBirth DATE) RETURNS INT AS
BEGIN
    RETURN DATEDIFF(YEAR, @DateOfBirth, GETDATE());
END;

Procedure: Do things. Function: Calculate things.

🎯 Q2: How do I get the ID of inserted row?

Use SCOPE_IDENTITY():

INSERT INTO Student (Name) VALUES ('Ravi');
SET @NewId = SCOPE_IDENTITY();  -- Gets inserted ID

SCOPE_IDENTITY(): Last identity value in current scope.

@@IDENTITY: Last identity anywhere (less safe).

Use SCOPE_IDENTITY.

🎯 Q3: What does OUTPUT parameter do?

Returns value from procedure to caller:

CREATE PROCEDURE GetCount @ClassName VARCHAR(10), @Count INT OUTPUT
AS
BEGIN
    SELECT @Count = COUNT(*) FROM Student WHERE ClassName = @ClassName;
END;

DECLARE @Total INT;
EXECUTE GetCount 'Class10', @Count = @Total OUTPUT;
SELECT @Total;  -- Returns count value

OUTPUT: Procedure sets, caller receives.

🎯 Q4: Should I use TRY-CATCH in every procedure?

Not required for simple operations:

CREATE PROCEDURE GetStudent @Id INT AS
BEGIN
    SELECT * FROM Student WHERE StudentId = @Id;
END;
-- Simple SELECT: no error handling needed

Required for INSERT/UPDATE with logic:

CREATE PROCEDURE InsertStudent @Name VARCHAR(100) AS
BEGIN
    TRY-CATCH: Validate, insert, handle errors
END;

Use TRY-CATCH when: validation, multiple steps, error matters.

🎯 Q5: What's the difference between scalar and table-valued functions?

Scalar: Returns single value.

CREATE FUNCTION GetAverage (@ClassId INT) RETURNS DECIMAL(5,2)
AS BEGIN RETURN 85.5; END;

SELECT dbo.GetAverage(1);  -- Returns 85.5

Table-valued: Returns result set (table).

CREATE FUNCTION TopStudents (@N INT) RETURNS TABLE
AS RETURN (SELECT TOP (@N) * FROM Student ORDER BY ...);

SELECT * FROM dbo.TopStudents(10);  -- Returns 10 rows

Scalar: Single value. Table: Full result set.

🎯 Q6: How do I call a procedure from backend code?

SQL Server:

EXECUTE ProcedureName @Param1 = Value1, @Param2 = Value2;

C# (ADO.NET):

using (SqlCommand cmd = new SqlCommand("ProcedureName", connection))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Param1", value1);
    cmd.ExecuteNonQuery();
}

C# (Entity Framework):

var result = dbContext.Database.SqlQuery<ResultType>(
    "EXECUTE ProcedureName @Param1 = {0}", value1
);

Procedure name + parameters + execute.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Stored procedures and functions in SQL Server. Try these prompts:

• "Show procedure: insert exam result with validation (marks range, no duplicate)"
• "Create scalar function to calculate student percentage"
• "What's the difference between PROCEDURE and FUNCTION?"
• "How do I use TRY-CATCH for error handling in procedures?"
• "Quiz me: ask 5 questions about procedures, functions, OUTPUT, error handling"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

27. Indexes & Performance Tuning -- Speeding Up Queries ->