hii want to run stored procedure in synapse but ima getting an error ,can anyone help me

Question

query:

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://msinstorageaccount145.dfs.core.windows.net/gold/SalesLT/'+@ViewName+'/'',FORMAT = ''DELTA'') AS [result]'

EXEC (@statement)

END
GO

error i got is this
Error

Stored procedure1

{ "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 'SELECT'.", "failureType": "UserError", "target": "Stored procedure1", "details": [] }
can anyone help please

Answer 1

I am assuming you may need only the Azure SQL Database or Azure Synapse Analytics tag here, I updated your query like the following :

USE gold_db;
GO

-- Optionally, drop the existing procedure if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'CreateSQLServerlessView_gold')
    DROP PROCEDURE CreateSQLServerlessView_gold;
GO

CREATE PROCEDURE CreateSQLServerlessView_gold @ViewName nvarchar(100)
AS
BEGIN
    DECLARE @statement NVARCHAR(MAX);
    SET @statement = N'CREATE VIEW ' + QUOTENAME(@ViewName) + N' AS 
        SELECT * FROM OPENROWSET(
            BULK ''https://msinstorageaccount145.dfs.core.windows.net/gold/SalesLT/' + @ViewName + '/'',
            FORMAT = ''CSV''
        ) AS result;';
    EXEC sp_executesql @statement;
END;
GO

Answer 2

Hi naveena kankipati
The code you provided looks correct and should create a SQL Serverless view that reads data from a Delta Lake table stored in Azure Data Lake Storage Gen2. The error message you received earlier might have been caused by a temporary issue or a typo in the SQL statement. If you encounter the error again, please double-check the syntax of the SQL statement and make sure that all the required parameters are provided correctly. If you have any further questions or issues, please let me know.