How to create a Stored Procedure in the Microsoft Fabric Warehouse

João Barros 09 de November de 2026 6 min read

This tutorial shows how to create a Stored Procedure in a Microsoft Fabric Warehouse to encapsulate reusable T-SQL logic. A Stored Procedure helps automate transformations, reduce network traffic and improve the management of repeated queries. In practical scenarios, moving aggregations to the server can reduce the volume of data transmitted to the client by more than 90% (for example, sending 7 summary rows instead of 100k detail rows), and decrease query latency from seconds to hundreds of milliseconds when the Warehouse is well indexed.

Prerequisites

An account with access to Microsoft Fabric and permissions for a Workspace and a Warehouse. You typically need SQL execution permissions on the Warehouse (for example, a role that allows running queries or administration of the Warehouse).
Basic familiarity with T-SQL (SELECT, INSERT, UPDATE). Minimal knowledge of transactions and concurrency controls is useful.
A Warehouse configured and online in the Workspace where you will run the code. Verify that the SQL endpoint is active and that the Warehouse has sufficient compute power — for example, for 1M rows a medium-sized Warehouse usually executes the aggregation in tens of seconds.

Step 1: Open the Warehouse and start a T-SQL session

Open the Workspace in Microsoft Fabric, select your Warehouse and click "New SQL Script" or "Query" to start a T-SQL session. The goal is to execute the code to create the Stored Procedure in the context of the Warehouse. Confirm the database/schema context at the top of the editor (for example, dbo) and, if necessary, run a USE <DatabaseName> before creation to ensure the procedure is placed in the correct schema.

Step 2: Plan the Stored Procedure logic

Decide what the Stored Procedure will do. Practical example: a routine that aggregates sales by day and inserts the results into a summary table. Consider parameters (e.g. @StartDate and @EndDate), expected cardinality (one row per day: 7 rows for a week, 30 for a month) and idempotency requirements (running multiple times should not create duplicates). Identify key columns for indexing — in the case of sales, an index on OrderDate or partitioning by date can greatly improve performance on datasets with millions of rows.

Step 3: Create the summary table (if needed)

If not already present, create a destination table in the same Warehouse schema to store the daily summary. For moderate volumes, a simple schema like the one below is sufficient; for higher volumes consider compression, indexes or partitions by month/year.

CREATE TABLE IF NOT EXISTS dbo.Sales_DailySummary (
  SalesDate date NOT NULL,
  TotalAmount decimal(18,2) NOT NULL,
  TotalOrders int NOT NULL,
  LastUpdated datetime2 NOT NULL
);

Tip: if you expect frequent updates, create an index on SalesDate to speed up MERGE/UPDATEs. In tests, a summary table with 10k rows updates in milliseconds, while a source with 1M rows can take 10-60 seconds depending on the Warehouse.

Step 4: Write the Stored Procedure

Create the Stored Procedure with parameters for the date window. Use transactions and simple error handling to ensure consistency. The example uses MERGE to insert/update in bulk; this avoids round-trips and is efficient when the number of days in the interval is small compared to the source rows.

CREATE OR ALTER PROCEDURE dbo.usp_UpdateDailySalesSummary
  @StartDate date,
  @EndDate date
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRY
    BEGIN TRANSACTION;

    -- Calculate summary and insert/update into the target table
    MERGE dbo.Sales_DailySummary AS target
    USING (
      SELECT CAST(OrderDate AS date) AS SalesDate,
             SUM(Amount) AS TotalAmount,
             COUNT(1) AS TotalOrders
      FROM dbo.Sales
      WHERE OrderDate BETWEEN @StartDate AND @EndDate
      GROUP BY CAST(OrderDate AS date)
    ) AS src
    ON target.SalesDate = src.SalesDate
    WHEN MATCHED THEN
      UPDATE SET TotalAmount = src.TotalAmount,
                 TotalOrders = src.TotalOrders,
                 LastUpdated = SYSUTCDATETIME()
    WHEN NOT MATCHED THEN
      INSERT (SalesDate, TotalAmount, TotalOrders, LastUpdated)
      VALUES (src.SalesDate, src.TotalAmount, src.TotalOrders, SYSUTCDATETIME());

    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    DECLARE @ErrMsg nvarchar(4000) = ERROR_MESSAGE();
    THROW 51000, @ErrMsg, 1;
  END CATCH
END;

Notes: controlling XACT_STATE prevents leaving transactions open. The THROW with code 51000 indicates a custom error; in production log the error in audit tables or Warehouse logs.

Step 5: Grant permissions (optional)

If other users or services need to execute the Stored Procedure, grant EXECUTE to the appropriate role or user. For automations via a service principal, grant to the user associated with the service.

GRANT EXECUTE ON OBJECT::dbo.usp_UpdateDailySalesSummary TO [YourUserOrRole];

Principle of least privilege: grant only EXECUTE if the account does not need additional rights. In tighter security environments, prefer roles with controlled permission.

Step 6: Run and test the Stored Procedure

Call the Stored Procedure with a test date range. Verify that the logic correctly inserts/updates the records. Test first with a short range (e.g. 7 days) and confirm timings. If the source has 100k rows and the Warehouse is sized for medium loads, expect times between 5-30 seconds for aggregation.

EXEC dbo.usp_UpdateDailySalesSummary @StartDate = '2026-10-01', @EndDate = '2026-10-07';

-- Verify results
SELECT * FROM dbo.Sales_DailySummary
WHERE SalesDate BETWEEN '2026-10-01' AND '2026-10-07'
ORDER BY SalesDate;

Useful validations: compare SUM(TotalOrders) with SELECT COUNT(*) FROM dbo.Sales WHERE OrderDate BETWEEN ... to ensure integrity.

Verify the result

Confirm that the dbo.Sales_DailySummary table contains rows for each day in the range and that the TotalAmount and TotalOrders values match the aggregations from the dbo.Sales table. Also check the LastUpdated column to ensure the run was recent. If there are no results, review the WHERE condition (watch out for time zones and time portions in OrderDate) and the date format; try CAST/CONVERT if needed. If you notice discrepancies, run intermediate SELECTs to validate the aggregation and count nulls or rows with invalid dates.

Conclusion

You now have a Stored Procedure in the Microsoft Fabric Warehouse that aggregates sales by day and maintains a summary table. Next steps: schedule execution using a Pipeline in the Workspace itself or automate with Azure Logic Apps/Power Automate for periodic updates (e.g. daily at 01:00). Monitor execution times and use indexes on OrderDate if times grow. In production environments, test with samples and monitor metrics (CPU, time, I/O) — a typical daily run for 1M rows can take between 20-60 seconds depending on the Warehouse configuration.