stored proc to dynamically create view

Question

Hello,
Can you help me with building the stored procedure so that it creates view using dynamic sql?
I have difficulties with the single quote I think
Thank you

create procedure usp_Test
@SchemaName varchar(10),
@ObjectName varchar(20)
AS
declare @alenzi nvarchar(mx)
set @alenzi = '
CREATE OR ALTER VIEW ' + @SchemaName + '.' + @ObjectName AS
SELECT
*
FROM OPENROWSET(
BULK 'DBName/@ObjectName/year=/month=/day=*/**',
DATA_SOURCE = 'xyz',
FORMAT='PARQUET'
)
WITH(
DepartmentID INT,
Name NVARCHAR(100),
GroupName NVARCHAR(100),
ModifiedDate DATETIME2
)
AS [r]'

execute sp_executesql(@alenzi )

--------------------

exec usp_Test @SchemaName = 'b1', @objectName = 'Department'

--test if view is created
SELECT * FROM b1.Department

Answer 1

You need to use 2 single quotes inside a string.

set @sql = 'CREATE OR ALTER VIEW ' + @SchemaName + '.' + @ObjectName + ' AS
SELECT
FROM OPENROWSET(
BULK ''DBName/@ObjectName/year=/month=/day=/**'',
DATA_SOURCE = ''xyz'',
FORMAT=''PARQUET''
)
WITH(
DepartmentID INT,
Name NVARCHAR(100),
GroupName NVARCHAR(100),
ModifiedDate DATETIME2
)
AS [r]'