Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

Executing & Modifying Stored Procedures

SQL Server Stored Procedures can be executed using the EXEC statement and can be modified using ALTER PROCEDURE.

1. Introduction

A Stored Procedure is a precompiled collection of SQL statements that is stored inside the SQL Server database.

After creating a Stored Procedure, we can execute it whenever we need its functionality.

SQL Server provides the EXEC or EXECUTE statement to run a Stored Procedure. We can also modify an existing Stored Procedure using ALTER PROCEDURE.

2. Executing a Stored Procedure

The EXEC statement is used to execute a Stored Procedure.

EXEC ProcedureName;

The EXECUTE keyword can also be used.

EXECUTE ProcedureName;
Note:

EXEC is the commonly used short form of EXECUTE.

3. Simple Example

Consider a Stored Procedure that returns all students from the Students table.

CREATE PROCEDURE GetAllStudents
AS
BEGIN
    SELECT *
    FROM Students;
END;
Execute the Procedure
EXEC GetAllStudents;
Result:

The Stored Procedure executes the SELECT statement and returns all records from the Students table.

4. Executing with Input Parameters

A Stored Procedure can accept input parameters while executing.

CREATE PROCEDURE GetStudentById
    @StudentId INT
AS
BEGIN
    SELECT
        StudentId,
        Name,
        City,
        Age
    FROM Students
    WHERE StudentId = @StudentId;
END;
Execute the Procedure
EXEC GetStudentById
    @StudentId = 101;
Explanation:

Here, 101 is passed to the StudentId parameter. The procedure returns the student whose StudentId is 101.

5. Executing with Multiple Parameters

A Stored Procedure can accept multiple input parameters.

CREATE PROCEDURE GetStudentsByCityAndAge
    @City VARCHAR(50),
    @Age INT
AS
BEGIN

    SELECT
        StudentId,
        Name,
        City,
        Age
    FROM Students
    WHERE City = @City
      AND Age = @Age;

END;
Execute
EXEC GetStudentsByCityAndAge
    @City = 'Pune',
    @Age = 22;
Result:

The procedure returns students whose City is Pune and Age is 22.

6. EXEC vs EXECUTE

SQL Server supports both EXEC and EXECUTE for running Stored Procedures.

EXEC
EXEC GetAllStudents;

Short and commonly used form.

EXECUTE
EXECUTE GetAllStudents;

Full form of the EXEC statement.

7. Modifying a Stored Procedure

Once a Stored Procedure has been created, its definition can be changed using the ALTER PROCEDURE statement.

We do not need to drop the existing procedure before modifying it.

ALTER PROCEDURE ProcedureName
AS
BEGIN
    -- Modified SQL statements
END;

8. ALTER PROCEDURE Example

Suppose the following Stored Procedure already exists:

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

We can modify the procedure to return only selected columns.

ALTER PROCEDURE GetAllStudents
AS
BEGIN

    SELECT
        StudentId,
        Name,
        City
    FROM Students;

END;
Explanation:

The existing Stored Procedure is modified using ALTER PROCEDURE. The procedure name remains the same, but its SQL logic is changed.

9. Modifying Procedure Parameters

An existing Stored Procedure can also be modified to work with parameters.

ALTER PROCEDURE GetStudentsByCity
    @City VARCHAR(50)
AS
BEGIN

    SELECT
        StudentId,
        Name,
        City,
        Age
    FROM Students
    WHERE City = @City;

END;
Execute
EXEC GetStudentsByCity
    @City = 'Pune';

10. Modifying Procedure Logic

We can change the SQL statements inside a Stored Procedure without changing its name.

ALTER PROCEDURE GetStudentsByCity
    @City VARCHAR(50)
AS
BEGIN

    SELECT
        StudentId,
        Name,
        City,
        Age
    FROM Students
    WHERE City = @City
      AND Age > 18;

END;

After modification, the procedure returns only students whose age is greater than 18.

11. Execute the Modified Procedure

After modifying the Stored Procedure, we can execute it normally using EXEC.

EXEC GetStudentsByCity
    @City = 'Pune';
Result:

SQL Server executes the latest modified definition of the Stored Procedure.

12. Important Points

  • EXEC is used to execute a Stored Procedure.
  • EXECUTE can also be used to run a procedure.
  • Input parameters can be supplied while executing a Stored Procedure.
  • Multiple parameters can be passed to a procedure.
  • ALTER PROCEDURE is used to modify an existing Stored Procedure.
  • Altering a procedure does not require dropping the procedure first.
  • Always test a modified Stored Procedure before using it in a production application.

13. Real-World Example

Consider an application that needs to display students from a particular city.

First, create the Stored Procedure:

CREATE PROCEDURE GetStudentsByCity
    @City VARCHAR(50)
AS
BEGIN

    SELECT
        StudentId,
        Name,
        City,
        Age
    FROM Students
    WHERE City = @City;

END;

Execute it:

EXEC GetStudentsByCity
    @City = 'Pune';

Later, if the application requirement changes and we want to return only students above 18 years, modify it:

ALTER PROCEDURE GetStudentsByCity
    @City VARCHAR(50)
AS
BEGIN

    SELECT
        StudentId,
        Name,
        City,
        Age
    FROM Students
    WHERE City = @City
      AND Age > 18;

END;

Execute the modified procedure again:

EXEC GetStudentsByCity
    @City = 'Pune';
Use Case:

This approach allows developers to update the business logic of a Stored Procedure without creating a new procedure every time.

Summary

Stored Procedures are executed using EXEC or EXECUTE. Parameters can be supplied during execution. Existing Stored Procedures can be modified using ALTER PROCEDURE without dropping them first. After modification, the procedure can be executed normally using EXEC.