What are some examples of SQL stored procedures?

What are some examples of SQL stored procedures?

SQL stored procedures are precompiled collections of SQL statements that can be executed as a single unit. Here are a few examples:

1. Simple Procedure: A procedure that retrieves all records from a table.

   CREATE PROCEDURE GetAllEmployees AS
   BEGIN
       SELECT * FROM Employees;
   END;
   

2. Procedure with Parameters: A procedure that takes parameters to filter results.

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

3. Procedure with Logic: A procedure that includes conditional logic.

   CREATE PROCEDURE UpdateEmployeeSalary @EmployeeID INT, @NewSalary DECIMAL AS
   BEGIN
       IF EXISTS (SELECT * FROM Employees WHERE ID = @EmployeeID)
       BEGIN
           UPDATE Employees SET Salary = @NewSalary WHERE ID = @EmployeeID;
       END
   END;
   

Would you like to see more examples or details on a specific type?

Can you explain the benefits of using stored procedures?

Certainly! The benefits of using stored procedures include:

1. Performance: Stored procedures are precompiled, which can lead to faster execution times.
2. Security: They can help protect against SQL injection attacks by encapsulating the SQL code.
3. Maintainability: Changes can be made in one place without affecting the application code.
4. Reusability: Procedures can be reused across different applications, reducing redundancy.
5. Reduced Network Traffic: Executing a single stored procedure can minimize the number of calls between the application and the database.

What is the difference between a stored procedure and a function in SQL?

The main differences between stored procedures and functions in SQL are:

1. Return Type: Functions must return a value, while stored procedures do not have to return a value.
2. Usage: Functions can be used in SQL statements (like SELECT), whereas stored procedures cannot.
3. Transaction Control: Stored procedures can manage transactions (BEGIN, COMMIT, ROLLBACK), while functions cannot.
4. Side Effects: Functions are generally expected to be free of side effects, while stored procedures can modify data.

Can you provide a more complex example of a stored procedure?

Sure! Here's a more complex example of a stored procedure that handles inserting a new employee and updating the department's employee count:

CREATE PROCEDURE AddEmployee
    @Name NVARCHAR(100),
    @DepartmentID INT,
    @Salary DECIMAL
AS
BEGIN
    BEGIN TRANSACTION;
    BEGIN TRY
        INSERT INTO Employees (Name, DepartmentID, Salary)
        VALUES (@Name, @DepartmentID, @Salary);

        UPDATE Departments
        SET EmployeeCount = EmployeeCount + 1
        WHERE ID = @DepartmentID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

This procedure adds a new employee and updates the department's employee count in a single transaction.