Can I create a stored procedure in serverless sql pool

Question

Following the official documentation from Microsoft, it is possible to create a stored procedure in Azure Synapse Serverless SQL pool. I manage to execute the following script

Create proc Test
as
Select 1 as X

However, I Don't see the Test stored procedure in the Serverless SQL pool. I don't also see the programmability folder in the Serverless SQL pool

Answer 1

Yes, it is possible to create Stored procedure in Serverless SQL in Synapse Analytics, using T-SQL commands. I actually did that using SSMS and connecting to a SQL database in Synapse Analytics Workspace - using Serverless SQL endpoint link, provided in Overview window in Azure:

But for me there were caveats - schema [dbo] is explicitly prohibited, so I had to create a schema before executing CREATE PROCEDURE script.

Second caveat was that I had to do it twice. First time I executed the CREATE PROCEDURE nothing happened, even though SSMS reported success. Only after I executed it again, stored procedure showed up in Object Explorer. But I had connection issues prior and after, it may be linked to that, and nothing related to Serverless SQL.

PS. My SQL DB is actually DeltaLake, and tables are parquet files. Not sure it plays any role in this answer, but I include it, just in case.

Answer 2

The only way I've found to view the Stored Procedure in Synapse Studio or Azure Data Studio is to query the sys schema tables. However SPs are visible if you use SSMS via steps here https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/get-started-ssms

Query in Synapse Studio

SELECT definition, uses_ansi_nulls, uses_quoted_identifier, is_schema_bound  
FROM sys.sql_modules  
WHERE object_id = OBJECT_ID('dbo.Test');   
GO   

SSMS