Arabic
French
Spanish
We are the Best Consulting web site as part of the annual WebAward Competition!
Are you writing the same SQL code repeatedly? Thinking of a way to reuse the code? If yes, then you have come to the right place! Or if you want to know about stored procedure and how to create/execute them, this article is for you as well.
A stored procedure is a group of SQL statements that drops the need to write the same set of SQL statements repeatedly. It returns a set of results (if any) when executed. Users can also pass parameters when a stored procedure is created so values can be adjusted as per the requirements at hand.
Why use stored procedure?
- Write once, execute multiple times!
- Adjust parameter values as needed without writing a single SQL statement.
- Increased performance by storing query plan of the stored procedure.
Use cases of stored procedure
- Load fact and dimension tables using stored procedure.
- Audit table loading.
- Task maintenance in database/warehouse/lakehouse.
- Apply business logic and rules in warehouse/lakehouse/database.
- Automate daily database operations.
Prerequisites
- A Fabric-enabled workspace.
- Workspace level access at least contributor.
Objective
Learn how to create and execute a stored procedure in a Microsoft Fabric warehouse using T-SQL.
Creating Stored Procedure
- Navigate to the right workspace and select a warehouse where you want to create the stored procedure.
2. Click on ‘New SQL Query’ and create a blank SQL query. Or you can use the stored procedure template.
3. Write the below T-SQL statement to create and execute the stored procedure.
The above SQL statements does the following,
- CREAT PROC [dbo].[LoadOrders] – creates a stored procedure in the dbo schema named LoadOrders.
- @MoreThan float – MoreThan is the declared parameter to adjust the values as per requirements while executing the stored procedure. The data type of the parameter is float.
- BEGIN, END – start and end of the statement.
- Drop table if exists … – if there is any table named OrdersMoreThan in the dbo schema, this line of SQL code will remove it form the schema.
- SELECT * INTO……. – returns a table named OrdersMoreThan where LineItemTotal is greater than the declared parameter.
- Exec [dbo].[LoadOrders]….. – Executes the stored procedure where the parameter @MoreThan value is 10000.
4. A new table called ‘OrdersMoreThan’ in the dbo schema will be created upon successful completion of the query run.
In this article, we learned how to create and execute a stored procedure, what it is, why to use it, its use cases, and the prerequisites to create a stored procedure in Microsoft Fabric. In summary, a stored procedure can be helpful when used correctly. It reduces the need to write code repeatedly, thus ensuring scalability and reusability.
Tags:
microsoft fabric
Recent News
Working with Data Activator in Microsoft
16th February 2024
Transformations at Scale: Using Microsoft Fabric
9th April 2024
To Pie or not to Pie
9th October 2024Stay ahead in a rapidly world. Subscribe to Prysm Insights,our monthly look at the critical issues facing global business.
[mc4wp_form]
We come with an experienced engineering and management team to offer strategy and consulting, technology, and operations services.
© 2024 Data Crafters | All rights reserved