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