create store procedure in Synapse

Question

Hi ,

How to create store procedure in synapse to create tables

Regards

Answer 1

@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.

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

EXEC CreateNewTable

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.

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.