T-SQL Stored Procedures

What are Stored Procedures?

Stored procedures are a set of T-SQL statements that are compiled and stored on the database server. They offer a way to encapsulate business logic directly within the database, improving performance, security, and maintainability.

Key benefits include:

• Performance: Pre-compiled execution plans reduce overhead.
• Reusability: Write code once, call it many times from different applications.
• Security: Grant execute permissions instead of direct table access.
• Maintainability: Centralized logic makes updates easier.
• Reduced Network Traffic: Send one command instead of multiple SQL statements.

Creating a Stored Procedure

Use the CREATE PROCEDURE (or CREATE PROC) statement to define a new stored procedure. You can include input and output parameters.

Syntax

CREATE PROCEDURE schema_name.procedure_name
    @parameter1 data_type [ = default_value ],
    @parameter2 data_type OUTPUT
AS
BEGIN
    -- T-SQL statements go here
    SET NOCOUNT ON; -- Recommended for performance

    -- Example logic
    SELECT column1, column2
    FROM YourTable
    WHERE column1 = @parameter1;

    SET @parameter2 = @@ROWCOUNT; -- Assign output value
END;
GO

Explanation:

• schema_name: Optional, defaults to dbo.
• procedure_name: The unique name for your procedure.
• @parameter1: An input parameter.
• OUTPUT: Denotes an output parameter.
• SET NOCOUNT ON;: Prevents SQL Server from sending messages indicating the number of rows affected by a T-SQL statement, which can improve performance.
• GO: A batch separator used by SQL Server tools.

Executing a Stored Procedure

Use the EXECUTE (or EXEC) command to run a stored procedure. Pass arguments for input parameters and capture values from output parameters.

Syntax

-- Executing with input parameters
EXECUTE schema_name.procedure_name @parameter1 = 'some_value';

-- Executing and capturing output parameters
DECLARE @output_param INT;
EXECUTE schema_name.procedure_name
    @parameter1 = 'another_value',
    @parameter2 = @output_param OUTPUT;

SELECT @output_param AS RowsAffected;

-- Using shorthand
EXEC schema_name.procedure_name 'value_for_param1';
GO

Modifying and Dropping Procedures

You can alter existing procedures or remove them entirely.

ALTER PROCEDURE

Used to modify the definition of an existing stored procedure. The syntax is similar to CREATE PROCEDURE.

ALTER PROCEDURE schema_name.procedure_name
    @new_parameter INT
AS
BEGIN
    -- Updated T-SQL logic
    UPDATE YourTable SET SomeColumn = @new_parameter WHERE ID = 1;
END;
GO

DROP PROCEDURE

Used to delete a stored procedure from the database.

DROP PROCEDURE schema_name.procedure_name;
GO

You can use IF EXISTS to safely drop a procedure.

IF OBJECT_ID('schema_name.procedure_name', 'P') IS NOT NULL
    DROP PROCEDURE schema_name.procedure_name;
GO

Common Use Cases

• Data Validation: Encapsulate complex validation rules.
• Batch Processing: Execute a series of SQL statements as a single unit.
• Reporting: Generate complex reports by fetching and formatting data.
• Data Manipulation: Implement common data insertion, update, or deletion patterns.
• API Backends: Provide a secure and efficient interface for applications.

Advanced Concepts

• Dynamic SQL: Building SQL statements as strings and executing them (use with caution!).
• Error Handling: Using TRY...CATCH blocks for robust error management.
• Transaction Management: Ensuring data consistency with BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION.
• Recursion: Stored procedures that call themselves (for hierarchical data, etc.).
• Permissions: Managing who can execute specific procedures.