create store procedure in Synapse

Vineet S 225 Reputation points
2024-05-20T05:47:38.0366667+00:00

Hi ,

How to create store procedure in synapse to create tables

Regards

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,553 questions
{count} votes

1 answer

Sort by: Most helpful
  1. PRADEEPCHEEKATLA-MSFT 82,196 Reputation points Microsoft Employee
    2024-05-20T06:10:44.9+00:00

    @Vineet S - Thanks for the question and using MS Q&A platform.

    To create a stored procedure in Azure Synapse Analytics, you can use the SQL Server Management Studio (SSMS) or the Query Editor in the Azure portal. Here is an example of how to create a stored procedure that creates a table in Synapse Analytics using the Query Editor.

    Step1: In the Azure portal, navigate to your Synapse Analytics workspace and select the "Develop" hub.

    Step2: Open the Query Editor and connect to your dedicated SQL pool.

    Step3: In the query window, enter the following SQL code to create a stored procedure that creates a table:

    CREATE PROCEDURE CreateNewTable
    AS
    BEGIN
        CREATE TABLE [dbo].[NewTable]
        (
            [ID] INT NOT NULL,
            [Name] VARCHAR(50) NOT NULL,
            [Age] INT NOT NULL,
            CONSTRAINT [PK_NewTable] PRIMARY KEY NONCLUSTERED (ID) NOT ENFORCED,
            CONSTRAINT [UQ_Name] UNIQUE(Name) NOT ENFORCED
        )
    END
    

    Step4: Click the "Run" button to execute the query and create the stored procedure.User's image

    Step5: To execute the stored procedure and create the table, you can use the following SQL code:

    EXEC CreateNewTable
    

    User's image

    This will execute the stored procedure and create the "NewTable" in your dedicated SQL pool. You can modify the SQL code in the stored procedure to create tables with different columns and data types as per your requirement.

    User's image

    This article: Using stored procedures for dedicated SQL pools in Azure Synapse Analytics provides tips for developing dedicated SQL pool solutions by implementing stored procedures.

    Hope this helps. Do let us know if you any further queries.


    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.