question

arkiboys avatar image
0 Votes"
arkiboys asked TomPhillips-1744 answered

stored proc to dynamically create view

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 @sql nvarchar(mx)
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]'

execute sp_executesql(@sql)


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

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

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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]'
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.