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;
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;
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;
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;
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;
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';
SQL Server executes the latest modified definition of the Stored Procedure.
12. Important Points
-
EXECis used to execute a Stored Procedure. -
EXECUTEcan 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 PROCEDUREis 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';
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.