Stored Procedures

SQL Stored Procedures for SQL Server

In SQL Server, stored procedures are precompiled and stored in the database. They can be called multiple times without recompilation, providing a way to modularize and reuse SQL code. Here's how you can create and use stored procedures in SQL Server:

Creating a Stored Procedure:

            CREATE PROCEDURE procedure_name
            @parameter1 datatype,
            @parameter2 datatype
        AS
        BEGIN
            -- SQL statements
        END;
        

• procedure_name: The name of the stored procedure.
• @parameter1, @parameter2: Input parameters for the stored procedure.
• datatype: Data type of the parameters.
• AS: Keyword to indicate the beginning of the stored procedure's body.
• BEGIN...END: Block that contains the SQL statements to be executed.

Example:

                CREATE PROCEDURE GetEmployeeByID
                @EmployeeID INT
            AS
            BEGIN
                SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
            END;
            

Executing a Stored Procedure:

EXEC procedure_name @parameter1 = value1, @parameter2 = value2;

• procedure_name: The name of the stored procedure to execute.
• @parameter1, @parameter2: Input parameters and their corresponding values.

Example:

EXEC GetEmployeeByID @EmployeeID = 1;

Altering a Stored Procedure:

            ALTER PROCEDURE procedure_name
            @parameter1 datatype,
            @parameter2 datatype
        AS
        BEGIN
            -- Updated SQL statements
        END;
        

Example:

            ALTER PROCEDURE GetEmployeeByID
            @EmployeeID INT,
            @FirstName NVARCHAR(50)
        AS
        BEGIN
            SELECT * FROM Employees WHERE EmployeeID = @EmployeeID AND FirstName = @FirstName;
        END;
        

Dropping a Stored Procedure:

DROP PROCEDURE procedure_name;

Example:

DROP PROCEDURE GetEmployeeByID;

Stored procedures in SQL Server can accept input parameters, output parameters, and return values. They can also contain control-of-flow statements, transactions, error handling, and more, providing powerful capabilities for data manipulation and processing.