How to Create Stored Procedure in Serverless SQL Pool Database -Azure Synapse Analytics Tutorial

🛠️ How to Create Stored Procedure in Serverless SQL Pool | Azure Synapse Analytics

A Stored Procedure is a saved collection of T-SQL statements that you can execute as a unit. In Azure Synapse Analytics Serverless SQL Pool, you can use stored procedures to encapsulate business logic, complex queries, or reusable operations over data stored in your Data Lake.

✅ Why Use Stored Procedures?

• Encapsulate logic and reuse easily
• Improve manageability and organization of queries
• Reduce query complexity for end users
• Enable parameterized query execution

📘 Syntax Overview

CREATE PROCEDURE [schema].[ProcedureName]
AS
BEGIN
    -- your SQL logic here
END;

🧪 Example: Create and Execute a Stored Procedure

-- ==========================================
-- 📚 What is a Stored Procedure?
-- ==========================================
-- A Stored Procedure is a saved collection of SQL statements 
-- that can be executed as a program. 
-- Stored Procedures can accept parameters, perform queries, and return results.
-- In Serverless SQL Pool, Stored Procedures are used mainly for SELECT queries 
-- and metadata operations (no INSERT, UPDATE, DELETE).
-- You can not create the SP in master DB, Also you can see the SP from GUI.

-- ==========================================
-- ✅ How to Create a Stored Procedure in Serverless SQL Pool
-- ==========================================

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrdersById
    @customer_id INT
AS
BEGIN
    SELECT 
        c.customer_id,
        c.fname,
        c.lastname
    FROM dbo.Customer c
    WHERE c.customer_id = @customer_id;
END;

-- ==========================================
-- ✅ How to Call (Execute) the Stored Procedure
-- ==========================================

-- Method 1: Using EXEC keyword
EXEC dbo.GetCustomerOrdersById @customer_id = 2;

-- Method 2: Short form (works in Synapse Studio)
dbo.GetCustomerOrdersById 1;

-- ==========================================
-- ✅ How to Check if a Stored Procedure Exists in Serverless SQL Pool
-- ==========================================

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
  AND ROUTINE_NAME = 'GetCustomerOrdersById';

-- ==========================================
-- 🔔 Notes:
-- - Always use CREATE OR ALTER to safely update stored procedures.
-- - In Serverless SQL Pool, only SELECT and metadata operations are allowed inside procedures.
-- - Stored Procedures are metadata objects; no physical storage is used.
-- - Stored Procedures are not visible in Synapse Studio GUI — use queries to manage them.

📌 Notes

• Stored procedures in Serverless SQL Pool are metadata-only and cannot modify physical data
• You can execute them using EXEC [procedure_name]
• They’re great for organizing logic for reports or dashboards

📺 Watch the Full Tutorial

Credit: This post was created with help from ChatGPT and Gemini.