uspgenerator.GeneratorUspStep_Sql - V
type: V ( view ), modify_date: 2021-11-03 19:26:04
RepoObject_guid: 4590291C-9D61-EB11-84DC-A81E8446D5B0
Columns
PK | Column Name | Data Type | NULL? | ID |
---|---|---|---|---|
1 |
|
|
||
2 |
|
|
||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
Indexes
PK_GeneratorUspStep_Sql
-
IndexSemanticGroup: no_group
-
[column-usp_id]; int
-
[column-Number]; int
-
-
PK, Unique, Real: 1, 1, 0
idx_GeneratorUspStep_Sql__2
-
IndexSemanticGroup: no_group
-
[column-usp_id]; int
-
-
PK, Unique, Real: 0, 0, 0
Column Details
sql_modules_definition
uspgenerator.GeneratorUspStep_Sql - V script
/*
--usage:
SELECT *
FROM [repo].[GeneratorUspStep_Sql]
ORDER BY [id]
, [RowNumber_PerUsp]
*/
CREATE View [uspgenerator].[GeneratorUspStep_Sql]
As
Select
usp_id = u.id
, t.Number
, u.has_logging
, BeginEnd.required_Begin_count
, BeginEnd.required_End_count
, BeginEnd.is_required_ELSE
--only information
, u.usp_fullname
, t.RowNumber_PerUsp
--, [t].[Depth]
--, [t].[is_condition]
--, [t].[Root_Sort]
--, [t].[Parent_Number]
--, [t].[Parent_Sort]
--, [t].[Sort]
--, [t].[child_PerParent]
--, [t].[Asc_PerParentChild]
--, [t].[Desc_PerParentChild]
, sql.SqlStep
----this list is too wide, we need a smaller list
--, AdocStep = CONCAT (
-- '|'
-- , step.Number
-- , CHAR(13)
-- , CHAR(10)
-- , '|'
-- , step.[Name]
-- , CHAR(13)
-- , CHAR(10)
-- , '|'
-- , step.[is_condition]
-- , CHAR(13)
-- , CHAR(10)
-- , '|'
-- , step.[log_source_object]
-- , CHAR(13)
-- , CHAR(10)
-- , '|'
-- , step.[log_target_object]
-- , CHAR(13)
-- , CHAR(10)
-- , '|'
-- , step.[log_flag_InsertUpdateDelete]
-- , CHAR(13)
-- , CHAR(10)
-- )
, AdocStep = Concat (
'|'
, step.Number
, Char ( 13 ) + Char ( 10 )
, '|'
, Char ( 13 ) + Char ( 10 )
, '*'
, step.Name
, '*'
, Char ( 13 ) + Char ( 10 )
, Char ( 13 ) + Char ( 10 )
, Iif(step.is_SubProcedure = 1
, '* ' + '`EXEC ' + step.Statement + '`' + Char ( 13 ) + Char ( 10 )
, Null)
, Iif(step.is_condition = 1
, '* ' + '`IF ' + step.Statement + '`' + Char ( 13 ) + Char ( 10 )
, Null)
, Iif(step.log_flag_InsertUpdateDelete <> ''
, '* ' + step.log_flag_InsertUpdateDelete + Char ( 13 ) + Char ( 10 )
, Null)
, Iif(step.log_source_object <> ''
, '* ' + step.log_source_object + Char ( 13 ) + Char ( 10 )
, Null)
, Iif(step.log_target_object <> ''
, '* ' + step.log_target_object + Char ( 13 ) + Char ( 10 )
, Null)
, Char ( 13 ) + Char ( 10 )
, Char ( 13 ) + Char ( 10 ) + step.Description + Char ( 13 ) + Char ( 10 )
, Char ( 13 ) + Char ( 10 )
, '.Statement' + Char ( 13 ) + Char ( 10 )
, '[%collapsible]' + Char ( 13 ) + Char ( 10 )
, '=====' + Char ( 13 ) + Char ( 10 )
, '[source,sql,numbered]' + Char ( 13 ) + Char ( 10 )
, '----' + Char ( 13 ) + Char ( 10 )
, step.Statement + Char ( 13 ) + Char ( 10 )
, '----' + Char ( 13 ) + Char ( 10 )
, '=====' + Char ( 13 ) + Char ( 10 )
, Char ( 13 ) + Char ( 10 )
, '|'
, step.Parent_Number
, Char ( 13 ) + Char ( 10 )
, Char ( 13 ) + Char ( 10 )
)
From
uspgenerator.GeneratorUsp As u
Cross Apply uspgenerator.ftv_GeneratorUspStep_tree ( id, Null ) As t
Left Join
(
Select
s.usp_id
, t.Number
, required_Begin_count = Sum ( Iif(t.Asc_PerParentChild = 1, 1, 0))
, required_End_count = Sum ( Iif(t.Desc_PerParentChild = 1, 1, 0))
, is_required_ELSE = Max ( t.is_required_ELSE )
From
uspgenerator.GeneratorUspStep As s
Cross Apply uspgenerator.ftv_GeneratorUspStep_tree ( usp_id, Number ) As t
Where
s.is_condition = 1
Group By
s.usp_id
, t.Number
) As BeginEnd
On
BeginEnd.usp_id = u.id
And BeginEnd.Number = t.Number
Left Join
uspgenerator.GeneratorUspStep As step
On
step.usp_id = u.id
And step.Number = t.Number
Cross Apply uspgenerator.ftv_GeneratorUspStep_sql (
u.id
, t.Number
, u.has_logging
, BeginEnd.required_Begin_count
, BeginEnd.required_End_count
, BeginEnd.is_required_ELSE
) As sql