-
Notifications
You must be signed in to change notification settings - Fork 10
/
spTriggerGenerateViews.sql
45 lines (38 loc) · 1.23 KB
/
spTriggerGenerateViews.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
CREATE PROC dbo.spTriggerGenerateViews @DropView BIT
AS
BEGIN
DECLARE @LoopCountMax INT = (SELECT COUNT(*) FROM LDW.vwViewMetadata),
@LoopCount INT = 1,
@ExternalDataSourceDataLake NVARCHAR(255),
@FileFormat NVARCHAR(50),
@Location NVARCHAR(255),
@HeaderRow NVARCHAR(5),
@FieldTerminator NCHAR(1),
@ViewName NCHAR(50),
@FolderHierarchyDepth TINYINT,
@MaxVarcharValue SMALLINT
WHILE @LoopCount <= @LoopCountMax
BEGIN
SELECT @FileFormat = FileFormat,
@HeaderRow = ISNULL(HeaderRow,''),
@FieldTerminator = ISNULL([FieldTerminator],''),
@ViewName = ViewName,
@ExternalDataSourceDataLake = ExternalDataSourceDataLake,
@Location = [Location],
@FolderHierarchyDepth = FolderHierarchyDepth,
@MaxVarcharValue = MaxVarcharValue
FROM LDW.vwViewMetadata
WHERE ViewCreationOrder = @LoopCount
EXEC LDW.GenerateViews
@FileFormat,
@HeaderRow,
@FieldTerminator,
@ViewName,
@ExternalDataSourceDataLake,
@Location,
@FolderHierarchyDepth,
@DropView,
@MaxVarcharValue
SET @LoopCount = @LoopCount + 1
END
END