uspgenerator.GeneratorUspStep_Sql - V

type: V ( view ), modify_date: 2021-11-03 19:26:04

RepoObject_guid: 4590291C-9D61-EB11-84DC-A81E8446D5B0

Description

Examples

Entity Diagram

entity-uspgenerator.generatoruspstep_sql

Columns

Table 1. Columns of uspgenerator.GeneratorUspStep_Sql - V
PK Column Name Data Type NULL? ID

1

int

NOT NULL

2

int

NULL

nvarchar(max)

NOT NULL

tinyint

NOT NULL

int

NULL

int

NULL

int

NULL

bigint

NULL

nvarchar(max)

NOT NULL

nvarchar(261)

NOT NULL

Foreign Key Diagram

entity_1_1_fk-uspgenerator.generatoruspstep_sql

References

Object Reference Diagram - 1 1

entity_1_1_objectref-uspgenerator.generatoruspstep_sql

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-uspgenerator.generatoruspstep_sql

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-uspgenerator.generatoruspstep_sql

Column Reference Diagram

entity_1_1_colref-uspgenerator.generatoruspstep_sql

Indexes

PK_GeneratorUspStep_Sql

idx_GeneratorUspStep_Sql__2

Column Details

_

usp_id

1

usp_id

int

NOT NULL

Referenced Columns

Number

2

Number

int

NULL

AdocStep

AdocStep

nvarchar(max)

NOT NULL

has_logging

has_logging

tinyint

NOT NULL

is_required_ELSE

is_required_ELSE

int

NULL

required_Begin_count

required_Begin_count

int

NULL

required_End_count

required_End_count

int

NULL

RowNumber_PerUsp

RowNumber_PerUsp

bigint

NULL

SqlStep

SqlStep

nvarchar(max)

NOT NULL

usp_fullname

usp_fullname

nvarchar(261)

NOT NULL

Description

(concat('[',[usp_schema],'].[',[usp_name],']'))


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