Stored Procedure Practice Example
1. Import CSV data using store procedure in SQL server :
We have one Orders CSV file :
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
2. Inserts aggregated data into the table :
We have one OrderDetails table in SQL Server:
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“.
- After this, a new Job window will be opened. And in that, first, we need to specify the job name, owner, category, and description.
- Now, open the next Steps page and click on the New option. And then, provide the Step Name, Type, Database, and procedure execution command.
- 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.
- 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.
- And with all these steps, we have created an Agent job that will execute the stored procedure daily.
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.
