Stored Procedures

Stored procedures are a set of SQL statements that are created and stored in the database. They can be executed by name, making them a powerful tool for encapsulating complex logic, improving performance, and enhancing security.

What are Stored Procedures?

A stored procedure is a compiled collection of one or more Transact-SQL statements that you can execute as a unit. They can accept input parameters and return output parameters, allowing for dynamic execution based on various conditions.

Benefits of Stored Procedures

• Performance: Procedures are compiled and stored, which can lead to faster execution compared to ad-hoc SQL statements. The execution plan is cached, reducing overhead.
• Reusability: Complex operations can be encapsulated into a single procedure, which can then be called from multiple applications or scripts.
• Modularity: They promote a modular approach to database programming, making code easier to manage and update.
• Security: Permissions can be granted on stored procedures, allowing users to execute specific actions without granting direct access to the underlying tables.
• Reduced Network Traffic: Instead of sending multiple SQL statements over the network, only the call to the stored procedure is sent.

Creating a Stored Procedure

The basic syntax for creating a stored procedure is as follows:

CREATE PROCEDURE procedure_name
                @parameter1 datatype = default_value,
                @parameter2 datatype OUTPUT
            AS
            BEGIN
                -- SQL statements to be executed
                SELECT column1, column2 FROM some_table WHERE id = @parameter1;
                SET @parameter2 = 'Result of the procedure';
            END
            GO
            

Syntax Breakdown:

• CREATE PROCEDURE procedure_name: Initiates the creation of a new stored procedure.
• @parameter1 datatype = default_value: Defines an input parameter with its data type and an optional default value.
• @parameter2 datatype OUTPUT: Defines an output parameter with its data type.
• AS: Separates the procedure definition from its body.
• BEGIN...END: Encloses the block of SQL statements that form the procedure's logic.
• GO: A batch separator used by SQL Server Management Studio and other tools.

CREATE PROCEDURE GetCustomerByID
                    @CustomerID INT
                AS
                BEGIN
                    SELECT CustomerID, CompanyName, ContactName, City
                    FROM Customers
                    WHERE CustomerID = @CustomerID;
                END
                GO
                

Executing a Stored Procedure

You can execute a stored procedure using the EXECUTE or EXEC command.

EXECUTE procedure_name [parameter1_value, parameter2_value, ...];
            GO

            -- Example using the GetCustomerByID procedure
            EXECUTE GetCustomerByID 10;
            GO
            

Executing with Output Parameters

When dealing with output parameters, you need to declare variables to hold the output values.

DECLARE @OutputValue VARCHAR(100);
            EXEC GetCustomerDetails @CustomerID = 5, @CustomerName = @OutputValue OUTPUT;
            SELECT @OutputValue AS CustomerName;
            GO
            

Modifying and Deleting Stored Procedures

Modifying a Stored Procedure:

You can alter an existing stored procedure using ALTER PROCEDURE. This is similar to CREATE PROCEDURE but modifies an existing one.

ALTER PROCEDURE procedure_name
                @new_parameter datatype
            AS
            BEGIN
                -- Updated SQL statements
            END
            GO
            

Deleting a Stored Procedure:

Use the DROP PROCEDURE statement to remove a stored procedure from the database.

DROP PROCEDURE procedure_name;
            GO
            

Common Stored Procedure Scenarios

• Data Retrieval: Fetching specific data based on criteria.
• Data Manipulation: Performing INSERT, UPDATE, or DELETE operations.
• Batch Processing: Executing a sequence of operations.
• Administrative Tasks: Database maintenance and management tasks.