SQL Store Procedure | SQL Tutorial and Query Example

Text copied!

SQL Store Procedure

June 26, 2023, 5:46 p.m. under SQL
    • Here's the syntax :

    Without Parameter -

    Input Parameter :

    In SQL Server, store procedure accepts many or no parameter. A parameter is a value that is passed into a store procedure to perform specific task.

    Calling store procedure :

    Calling store procedure simply means executing or running it. To call a store procedure in SQL Server, you can use the 'EXEC' statement in a SQL query.

    CREATE PROCEDURE procedure_name
    @parameter1 data_type, -- optional
    @parameter2 data_type, -- optional
    ...
AS
BEGIN

    -- Write SQL statements here to perform desired operation

END
    Here's an example of Store Procedure without Parameter :

    [i] Let's assume you want to create a store procedure that retrieve only 'Male' employees data from [Employees] table.

    Here's an example of Store Procedure with a Parameter :

    [i] Let's assume you want to create a store procedure that accepts employee_id as input parameter from [Employees] table.

    To enhance your understanding of 'SQL store procedure', be sure to check out our next tutorial.
    EXEC store_procedure_name;
        
    EXEC store_procedure_name @Parameter = Value;
    SQL Store Procedure 
    CREATE PROCEDURE GetAllMaleEmployees
AS
BEGIN
    SELECT * FROM Employees
    WHERE Gender = 'Male';
END
    SQL Store Procedure 
    EXEC GetAllMaleEmployees;
    SQL Store Procedure 
    CREATE PROCEDURE GetAllEmployeesById
@Employee_Id INT
AS
BEGIN
    SELECT * FROM Employees
    WHERE Employee_Id = @Employee_Id;
END
    SQL Store Procedure 
    EXEC GetAllEmployeesById @Employee_Id = 1;
    SQL Store Procedure

    Frequently Asked Questions :

    A stored procedure in SQL is a prepared SQL code that you can save, so the code can be reused over and over again.
    SQL stored procedures are stored in the database server.
    The two types of stored procedures are:
a. System-defined stored procedures
b. User-defined stored procedures
    Stored procedures are used for performance optimization, security, and reusability of code in database operations.
    « Previous
    Next »
    RELATED :