SQL Stored Procedures Practice Example

Stored Procedure Practice Example

1. Import CSV data using store procedure in SQL server :

We have one Orders CSV file :

store-procedure-data-using-store-procedure-in-SQL-server

We want to import this CSV file’s data into SQL Server using a stored procedure.

You can download this sample import file from here:

First, we need to create a table in SQL Server with the same columns as the CSV file.

CREATE TABLE [Order] (
  OrderID int,
  CustomerID nvarchar(255),
  EmployeeID int,
  OrderDate nvarchar(255),
  RequiredDate nvarchar(255),
  ShippedDate nvarchar(255),
  ShipVia int,
  Freight float,
  ShipName nvarchar(255),
  ShipAddress nvarchar(255),
  ShipCity nvarchar(255),
  ShipRegion nvarchar(255),
  ShipPostalCode nvarchar(255),
  ShipCountry nvarchar(255) );

Order table:

Next, we create a stored procedure.

CREATE PROCEDURE ImportCSVData
@FilePath NVARCHAR(255) — Provide the path of the CSV file in this parameter
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = ‘BULK INSERT [Order]
FROM ”’ + @FilePath + ”’
WITH (
FIELDTERMINATOR = ”,”, — Specifies the delimiter used in the CSV file
ROWTERMINATOR = ”\n”, — Specifies the end of each row in the CSV file
FIRSTROW = 2 — This starts data insertion from the second row
);’;

EXEC sp_executesql @SQL;
END;
EXEC ImportCSVData @FilePath=’C:\ImpFilesInSQL\CSV Files\Order.csv’

SELECT * FROM [ORDER]

Explanation:

1. Creating the Stored Procedure

CREATE PROCEDURE ImportCSVData
    @FilePath NVARCHAR(255) — Provide the path of the CSV file in this parameter
AS
BEGIN 

CREATE PROCEDURE ImportCSVData: This line creates a stored procedure named ImportCSVData.
@FilePath NVARCHAR(255): This is a parameter for the stored procedure, where you will pass the file path of the CSV file. It can hold up to 255 characters.
BEGIN: Marks the beginning of the stored procedure’s logic.

2. Declaring a Variable:

DECLARE @SQL NVARCHAR(MAX);

DECLARE @SQL NVARCHAR(MAX): This line declares a variable named @SQL that can store a very long string of SQL code. NVARCHAR(MAX) allows the variable to hold a string of up to 2GB.

3. Setting Up the Bulk Insert Command:

SET @SQL = ‘BULK INSERT [Order]
FROM ”’ + @FilePath + ”’
WITH (
FIELDTERMINATOR = ”,”, — Specifies the delimiter used in the CSV file
ROWTERMINATOR = ”\n”, — Specifies the end of each row in the CSV file
FIRSTROW = 2 — It specifies the starting row number from which data should begin to
be inserted into a table.
);’;

SET @SQL =: This line assigns a string of SQL commands to the @SQL variable.
BULK INSERT Order: This command tells SQL Server to bulk insert data into the Order table.
FROM ”’ + @FilePath + ”’: The FROM clause specifies the source of the data. The @FilePath variable is inserted into the string, providing the file path to the CSV file.
WITH (FIELDTERMINATOR = ”,”): This option specifies the delimiter used in the CSV file (in this case, a comma).
ROWTERMINATOR = ”\n”: This option specifies the end of each row in the CSV file (typically a newline character).
FIRSTROW = 2: This option skips the first row of the CSV file (often used when the first row contains headers) and starts inserting data from the second row.

4. Executing the SQL Command:

EXEC sp_executesql @SQL;
END;

EXEC sp_executesql @SQL: This command executes the SQL statement stored in the @SQL variable.
END: Marks the end of the stored procedure’s logic.

5. Executing the Stored Procedure:

EXEC ImportCSVData @FilePath=’C:\ImpFilesInSQL\CSV Files\Ordertable.csv’;

EXEC ImportCSVData: This line calls the stored procedure ImportCSVData.
@FilePath=’C:\ImpFilesInSQL\CSV Files\Ordertable.csv’: This specifies the path to the CSV file that you want to import. Replace this path with the actual path to your CSV file.

6. Selecting Data from the Table:

SELECT * FROM [ORDER];

This command retrieves all records from the ORDER table after the data has been inserted.

Output

store-procedure-selecting-data-from-the-table

2. Inserts aggregated data into the table :

We have one OrderDetails table in SQL Server:

store-procedure-inserts-aggregated-data-into-table

We want to add summarize order information by calculating the total number of products (CountofProduct) and the total amount (Amount) for each order in another table using stored procedure.

For example, Order 10248 has 3 products with Product IDs 11, 42, and 72. The total amount is calculated as follows:

  • Product ID 11: 14 (price) * 12 (quantity) = 168
  • We then perform the same calculation for Product IDs 42 and 72, and sum these values to obtain the total amount, which is then stored in the OrderCalc table.
  • TotalAmount = 440.

First, we must create a table in SQL Server with the columns OrderId, CountofProduct, and Amount.

CREATE TABLE OrderCalc(

    OrderId int,

    CountOfProduct int,

    TotalAmount decimal

);

OrderCalc table:

Next, we create a stored procedure.

CREATE PROCEDURE InsertIntoOrderCalc

AS

INSERT INTO OrderCalc (OrderId, CountofProduct, Amount)

SELECT

    OrderId,

    COUNT(OrderId) AS CountofProduct,

    SUM(UnitPrice * Quantity) AS Amount

FROM

OrderDetails

GROUP BY OrderId;

 

EXEC InsertIntoOrderCalc;

 

SELECT * FROM OrderCalc;

Explanation:

1. Creating the Stored Procedure

CREATE PROCEDURE InsertIntoOrderCalc

AS

CREATE PROCEDURE InsertIntoOrderCalc : This line creates a stored procedure named InsertIntoOrderCalc.

2. Inserting Summarized Data into the OrderCalc Table:

INSERT INTO OrderCalc (OrderId, CountofProduct, Amount)

INSERT INTO OrderCalc (OrderId, CountofProduct, Amount):

This line specifies that the data will be inserted into the OrderCalc table. The columns that will be populated are OrderId, CountofProduct, and Amount.

SELECT OrderId,

 COUNT(OrderId) AS CountofProduct,

SUM(UnitPrice * Quantity) AS Amount

FROM OrderDetails

GROUP BY OrderId;

SELECT OrderId: The procedure retrieves the OrderId from the OrderDetails table.

COUNT(OrderId) AS CountofProduct: The COUNT function counts the number of entries for each OrderId. This count represents the total number of products associated with each order.

SUM(UnitPrice * Quantity) AS Amount: The SUM function calculates the total monetary value of the products in each order. It multiplies the UnitPrice by the Quantity for each product and sums these values for each OrderId.

FROM OrderDetails: The data is selected from the OrderDetails table, which contains detailed information about each order, including product prices and quantities.

GROUP BY OrderId: The GROUP BY clause groups the results by OrderId, ensuring that the COUNT and SUM functions are applied to each individual order, resulting in one row per order in the final output.

3. Executing the Stored Procedure:

EXEC InsertIntoOrderCalc;

EXEC ImportCSVData: This line calls the stored procedure ImportCSVData.

4. Selecting Data from the Table:

SELECT * FROM OrderCalc;

This query fetches all the records from the OrderCalc table. After the stored procedure has been executed, this command displays the summarized data for each order, including the OrderId, total CountofProduct, and total Amount.

Output

3. Scheduling the above task to run on daily bases:

Requirement is to schedule this procedure to automatically delete all data from the OrderCalc table and insert new data daily.

For this implementation, we will use the SQL Server Agent service. A SQL Server Agent is a service that is used to execute administrative tasks. Also, it allows to schedule execution of these administrative tasks also known as Jobs in SQL Server.

Let’s understand how we can schedule a stored procedure in SQL Server using SQL Server Management Studio. And for this task, we have to follow the given steps.

  • In the Management Studio, correct the database instance and then, expand the instance.
  • Next, expand the SQL Server Agent and right-click on the Jobs directory, and click on “New Job“.
scheduling the above task to run on
  • After this, a new Job window will be opened. And in that, first, we need to specify the job nameownercategory, and description.
store-procedure-scheduling-task-to run-on2
  • Now, open the next Steps page and click on the New option. And then, provide the Step NameTypeDatabase, and procedure execution command.
store-procedure-scheduling-task-to-run-3
  • Next, open the Schedule page and again click on the “New” option and then, specify the given options to schedule the execution. In the given example, we are scheduling the procedure to run daily.
store-procedure-scheduling-task-to-run4
  • In the end, click on the OK option to create a new job. After this, again right-click the Jobs directory and click on the “Start Job at Step” option.
store-procedure-scheduling-task-to-run-5
  • And with all these steps, we have created an Agent job that will execute the stored procedure daily.
store-procedure-scheduling-task-to-run6

4. To stop the scheduling of a job in SQL Server:

Expand the SQL Server Agent to see the list of jobs.

  • Under Jobs, locate the job you want to stop scheduling.
  • Right-click on the job and select Disable.

The job will no longer run at its scheduled intervals. You can re-enable it later if needed by selecting Enable instead of Disable.

Course Video:

4. Advanced Aggregation of Order Data Using ‘InsertIntoOrderCalc’ Stored Procedure:

 We have two tables in SQL Server OrderDetails and OrderCalc
OrderDetails:

OrderCalc table:

We want to summarize order information by calculating the total number of products (CountofProduct) and the total amount (Amount) for each order in the OrderCalc table using a stored procedure, similar to what we did in Example 2. Now, we want the procedure to handle new inserts or updates in the OrderDetails table.
For example, if a new record is inserted such as OrderId 10254 is added with ProductID 80, UnitPrice 14, Quantity 12, and Discount 0, or if any existing record in the OrderDetails table is updated (e.g., UnitPrice changes from 14 to another value), the stored procedure should:
      1. First check for any new orders in the OrderDetails table and insert them into the OrderCalc
          table.

      2. Then check for any updates to existing orders and update the corresponding records in
          OrderCalc based on the UpdateTime in OrderDetails is more recent than the UpdateTime in
          OrderCalc.

This ensure that the OrderCalc table always reflects the latest order information.

Query:

CREATE PROCEDURE InsertIntoOrderCalc
AS
BEGIN
— Insert new records into OrderCalc if they don’t already exist
    INSERT INTO OrderCalc (OrderId, CountofProduct, Amount, UpdateTime)
    SELECT
        OrderId,
        COUNT(OrderId) AS CountofProduct,
        SUM(UnitPrice * Quantity) AS Amount,
        MAX(UpdateTime) AS UpdateTime
  FROM OrderDetails WHERE OrderId NOT IN (SELECT OrderId FROM OrderCalc) GROUP BY OrderId;
— Update existing records in OrderCalc if the UpdateTime in OrderDetails is newer
  UPDATE oc
  SET
       oc.CountofProduct = od.CountofProduct,
       oc.Amount = od.Amount,
       oc.UpdateTime = od.UpdateTime
  FROM OrderCalc oc INNER JOIN
        (SELECT
             OrderId,
             COUNT(OrderId) AS CountofProduct,
             SUM(UnitPrice * Quantity) AS Amount,
             MAX(UpdateTime) AS UpdateTime
         FROM OrderDetails GROUP BY OrderId) od ON oc.OrderId = od.OrderId
     WHERE od.UpdateTime > oc.UpdateTime;
END;

Explanation:
1. Creating the Stored Procedure

CREATE PROCEDURE InsertIntoOrderCalc
AS
BEGIN

CREATE PROCEDURE InsertIntoOrderCalc : This line creates a stored procedure named InsertIntoOrderCalc.
The BEGIN keyword marks the start of the block of SQL statements that the procedure will execute.

2. Inserting New Records:
• Purpose: The first part of the procedure inserts new records into the OrderCalc table if they don’t already exist.

— Insert new records into OrderCalc if they don’t already exist
INSERT INTO OrderCalc (OrderId, CountofProduct, Amount, UpdateTime)

INSERT INTO OrderCalc: This command specifies that new records should be inserted into the
OrderCalc table.

   SELECT
       OrderId,
       COUNT(OrderId) AS CountofProduct,
       SUM(UnitPrice * Quantity) AS Amount,
       MAX(UpdateTime) AS UpdateTime
   FROM
       OrderDetails

SELECT Clause: The SELECT statement fetches the OrderId, the total count of products (CountofProduct), the total amount (Amount), and the latest update time (UpdateTime) from the OrderDetails table.

WHERE
      OrderId NOT IN (SELECT OrderId FROM OrderCalc)

WHERE Clause: The WHERE clause ensures that only those OrderIds from the OrderDetails table that do not already exist in the OrderCalc table are selected.

  GROUP BY
      OrderId;

GROUP BY Clause: The GROUP BY clause groups the OrderDetails records by OrderId, ensuring that the COUNT, SUM, and MAX functions are calculated per order.

3. Updating Existing Records:

• Purpose: The second part of the procedure updates existing records in the OrderCalc table if the corresponding records in the OrderDetails table have been modified (i.e., if the UpdateTime is more recent).

— Update existing records in OrderCalc if the UpdateTime in OrderDetails is newer
UPDATE oc
    SET
        oc.CountofProduct = od.CountofProduct,
        oc.Amount = od.Amount,
        oc.UpdateTime = od.UpdateTime
FROM
        OrderCalc oc

UPDATE oc: This command updates the records in the OrderCalc table, referenced by the alias oc.

SET Clause: The SET clause specifies that the CountofProduct, Amount, and UpdateTime columns in OrderCalc should be updated to match the values from the corresponding record in the OrderDetails table (referenced by the alias od).

INNER JOIN
        (SELECT
             OrderId,
             COUNT(OrderId) AS CountofProduct,
             SUM(UnitPrice * Quantity) AS Amount,
             MAX(UpdateTime) AS UpdateTime
FROM
             OrderDetails
         GROUP BY
             OrderId) od
ON
    oc.OrderId = od.OrderId

INNER JOIN: This INNER JOIN combines the OrderCalc and OrderDetails tables based on OrderId, matching each OrderCalc record with its corresponding OrderDetails record

The INNER JOIN combines the rows from the OrderCalc table (oc) with the rows from the subquery result (od) based on the OrderId.
The INNER JOIN matches rows from OrderCalc (oc) with rows from the subquery (od) where the OrderId is the same in both.
This means that for every OrderId in OrderCalc, if there is a corresponding OrderId in the subquery result, those rows will be combined.
Result: The join creates a temporary table where each row contains:
OrderId, CountofProduct, Amount, UpdateTime from OrderCalc
OrderId, CountofProduct, Amount, UpdateTime from the subquery (od).
The ON clause specifies the condition for matching rows between the two tables.
It ensures that only those rows are combined where OrderId in OrderCalc matches OrderId in the subquery result.

WHERE od.UpdateTime > oc.UpdateTime;

WHERE Clause: The WHERE clause ensures that only those records in OrderCalc are updated where the UpdateTime in OrderDetails is more recent than the UpdateTime in OrderCalc.
4. End Block:

End;

The END keyword signifies the end of the stored procedure’s block of SQL statements.

Summary:
The InsertIntoOrderCalc stored procedure first checks for any new orders in the OrderDetails table and inserts them into the OrderCalc table. It then checks for any updates in existing orders and updates the corresponding records in OrderCalc if the UpdateTime in OrderDetails is more recent.

Executing the Stored Procedure:

EXEC InsertIntoOrderCalc;

This is OrderDetail table:

We are going to insert a record with OrderId 10254, ProductId 11, UnitPrice 14, Quantity 12 and Discount 0.

INSERT INTO OrderDetails (OrderID, ProductId, UnitPrice, Quantity, Discount) VALUES
(10254 , 80, 14, 12, 0)

OrderCalc table Before:

After Executing InsertIntoOrderCalc Procedure:

OrderDetails Table:

Now we are going to update one record, changing the UnitPrice from 14 to 16 for OrderID 10254

UPDATE OrderDetails
SET UnitPrice=16
WHERE OrderID=10254

OrderCalc Table:

After Executing InsertIntoOrderCalc Procedure:

Record Updated.

Course Video:

Frequently Asked Questions

Still have a question?

Let's talk

SQL Stored Procedures are precompiled SQL statements that are stored in the database. They allow users to execute complex queries with parameters for reuse and efficiency.

Beginners can start with simple stored procedures that perform basic tasks like selecting data, and gradually move to procedures involving parameters, loops, and error handling. Example:

sqlCopy codeCREATE PROCEDURE GetAllEmployees  AS  BEGIN    SELECT * FROM Employees;  END;

  • Forgetting to define or pass parameters correctly.
  • Using incorrect data types for parameters.
  • Not testing procedures with various inputs.
  • Missing BEGIN…END blocks for complex procedures.
  1. Define the Procedure: Use CREATE PROCEDURE.
  2. Include Parameters (if needed): Define input/output parameters.
  3. Add SQL Statements: Include the desired logic.
  4. Test the Procedure: Execute it with different scenarios.

Example:

sqlCopy codeCREATE PROCEDURE GetEmployeeById @EmployeeId INT  AS  BEGIN    SELECT * FROM Employees WHERE id = @EmployeeId;  END;

  • Insert Data:

sqlCopy codeCREATE PROCEDURE AddEmployee @Name VARCHAR(50), @Age INT  AS  BEGIN    INSERT INTO Employees (name, age) VALUES (@Name, @Age);  END;

  • Update Data:

sqlCopy codeCREATE PROCEDURE UpdateEmployeeAge @EmployeeId INT, @Age INT  AS  BEGIN    UPDATE Employees SET age = @Age WHERE id = @EmployeeId;  END;

  • SQL Server Management Studio (SSMS)
  • MySQL Workbench
  • Oracle SQL Developer
  • Online SQL playgrounds like DB Fiddle and SQLZOO

Yes, you can adapt examples to include region-specific datasets, such as customer records or sales data for businesses operating in India.

Yes, use tools like SSMS or MySQL Workbench to debug stored procedures by stepping through the code and inspecting variable values.

Use the EXEC command or call the procedure directly. Example:

sqlCopy codeEXEC GetEmployeeById @EmployeeId = 1;