Meritshot Tutorials
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
-
SQL WHERE ClauseSQL WHERE Clause
-
SQL SELECT StatementSQL SELECT Statement
-
SQL DELETE StatementSQL DELETE Statement
-
SQL INSERT INTOSQL INSERT INTO
-
SQL Null ValuesSQL Null Values
-
SQL Update StatementSQL Update Statement
-
SQL Select TopSQL Select Top
-
SQL Aggregate FunctionsSQL Aggregate Functions
-
SQL LIKE and WildcardsSQL LIKE and Wildcards
-
SQL IN and SQL BETWEENSQL IN and SQL BETWEEN
-
SQL JOINSSQL JOINS
-
SQL Group BySQL Group By
-
SQL HavingSQL Having
-
SQL EXISTSSQL EXISTS
-
SQL SELECT INTOSQL SELECT INTO
-
SQL INSERT INTO SELECTSQL INSERT INTO SELECT
-
SQL CASE STATEMENTSQL CASE STATEMENT
-
SQL NULL FunctionsSQL NULL Functions
-
SQL Stored ProceduresSQL Stored Procedures
-
SQL User-Defined FunctionsSQL User-Defined Functions
-
SQL CommentsSQL Comments
-
SQL OperatorsSQL Operators
-
SQL Database Creation and ManagementSQL Database Creation and Management
-
SQL CREATE DATABASE StatementSQL CREATE DATABASE Statement
-
SQL CREATE TABLE StatementSQL CREATE TABLE Statement
-
SQL DROP DATABASE StatementSQL DROP DATABASE Statement
-
SQL DROP TABLE StatementSQL DROP TABLE Statement
-
SQL ALTER TABLE StatementSQL ALTER TABLE Statement
-
SQL NOT NULL ConstraintSQL NOT NULL Constraint
-
SQL UNIQUE ConstraintSQL UNIQUE Constraint
-
SQL PRIMARY KEY ConstraintSQL PRIMARY KEY Constraint
-
SQL FOREIGN KEY ConstraintSQL FOREIGN KEY Constraint
-
SQL CHECK ConstraintSQL CHECK Constraint
-
SQL DEFAULT ConstraintSQL DEFAULT Constraint
-
SQL IndexesSQL Indexes
-
SQL Date FunctionsSQL Date Functions
-
SQL ViewsSQL Views
-
SQL InjectionSQL Injection
-
SQL Data Types OverviewSQL Data Types Overview
-
SQL AUTO_INCREMENTSQL AUTO_INCREMENT
-
SQL Keywords ReferenceSQL Keywords Reference
SQL Stored Procedures
SQL Stored Procedures are precompiled collections of SQL statements that are stored in the database. They allow you to encapsulate complex SQL logic into reusable and manageable units, enhancing performance, security, and maintainability.
Purpose
- Encapsulation: Group related SQL commands into a single callable unit.
- Performance: Execute complex SQL operations more efficiently by precompiling the SQL code.
- Maintainability: Simplify code management and reuse by centralizing logic.
- Security: Restrict direct access to tables and control user interactions with the database.
Syntax
Creating a Stored Procedure:
CREATE PROCEDURE procedure_name
AS
BEGIN
— SQL statements
END;
Executing a Stored Procedure:
EXEC procedure_name;
Example:
1. Create a Stored Procedure to Get Employee Details
CREATE PROCEDURE GetEmployeeDetails
AS
BEGIN
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE employee_id = 1;
END;
Executing the Procedure:
EXEC GetEmployeeDetails;
Output:
employee_id | first_name | last_name | salary |
1 | Rajesh | Kumar | 55000 |
2. Create a Stored Procedure to Update Employee Salary
CREATE PROCEDURE UpdateEmployeeSalary
AS
BEGIN
UPDATE employees
SET salary = 60000
WHERE employee_id = 2;
END;
Executing the Procedure:
EXEC UpdateEmployeeSalary;
3. Create a Stored Procedure to Delete Employee Records
CREATE PROCEDURE DeleteEmployee
AS
BEGIN
DELETE FROM employees
WHERE employee_id = 3;
END;
Executing the Procedure:
EXEC DeleteEmployee;
4. Create a Stored Procedure with Multiple Statements
CREATE PROCEDURE ProcessEmployeeBonus
AS
BEGIN
BEGIN TRANSACTION;
— Update salary
UPDATE employees
SET salary = salary + 5000
WHERE employee_id = 1;
— Log the bonus addition
INSERT INTO bonus_log (employee_id, bonus_amount, log_date)
VALUES (1, 5000, GETDATE());
COMMIT TRANSACTION;
END;
Executing the Procedure:
EXEC ProcessEmployeeBonus;
Tips to Remember
- Keep Procedures Simple: Design stored procedures to be as straightforward and focused as possible to enhance readability and maintainability.
- Handle Errors: Implement error handling using TRY…CATCH blocks to manage exceptions and rollback transactions if necessary.
- Optimize Performance: Regularly review and optimize stored procedures to ensure they perform efficiently, especially for complex operations.
- Use Transactions Wisely: Employ transactions to ensure that a series of operations are executed as a single unit, preserving data integrity.
Frequently Asked Questions
Q1: Can stored procedures return multiple result sets?
A1: Yes, stored procedures can return multiple result sets, which can be useful for performing complex operations.
Example:
CREATE PROCEDURE GetEmployeeAndDepartmentDetails
AS
BEGIN
SELECT * FROM employees;
SELECT * FROM departments;
END;
Q2: How can I handle errors in stored procedures?
A2: Use TRY…CATCH blocks to handle errors and manage exceptions.
Example:
CREATE PROCEDURE UpdateEmployeeSalary
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
UPDATE employees
SET salary = 60000
WHERE employee_id = 2;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
— Handle error
END CATCH;
END;
Q3: Can stored procedures call other stored procedures?
A3: Yes, stored procedures can call other stored procedures.
Example:
CREATE PROCEDURE OuterProcedure
AS
BEGIN
EXEC InnerProcedure;
END;
Q4: What are some best practices for stored procedures?
A4: Best practices include keeping procedures simple, handling errors gracefully, optimizing performance, and using transactions to ensure data integrity.
