Error in Synapse Stored Procedure

Question

Hi I am getting below

{ "errorCode": "2402",
 "message": "Execution fail against sql server. Please contact SQL Server team if you need further support.
 Sql error number: 156. 
Error Message: Incorrect syntax near the keyword 'AS'.",
 "failureType": "UserError",
 "target": "Stored procedure1",
 "details": [] }

USE gold_db
GO

CREATE OR ALTER PROC CreateSQLServerlessView_gold @ViewName nvarchar(100) 
AS 
BEGIN 
   
DECLARE @statement VARCHAR(MAX)
    SET
        @statement = N'CREATE OR ALTER VIEW '+@ViewName+' AS
            SELECT *
            FROM
                OPENROWSET(
                BULK ''https://jaykumardeproject.dfs.core.windows.net/gold_db/Level_2_transformed_data/' + @ViewName + '/'',
                FORMAT = ''DELTA''  
            ) as [result]
        ' 
EXEC (@statement)

END
GO

Answer 1

Hello Jaykumar Patel,

Welcome to the Microsoft Q&A forum.

The issue seems to be with the syntax. Synapse doesn't support 'Alter'

Can you please remove 'OR ALTER' from your SP definition.

Reference document: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-stored-procedures

I hope this helps.