uspgenerator.GeneratorUsp_SqlUsp - V

type: V ( view ), modify_date: 2022-09-02 18:03:02

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

Description

Examples

Entity Diagram

entity-uspgenerator.generatorusp_sqlusp

Columns

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

nvarchar(max)

NOT NULL

tinyint

NOT NULL

nvarchar(4000)

NULL

uniqueidentifier

NULL

nvarchar(max)

NULL

nvarchar(max)

NOT NULL

nvarchar(max)

NULL

nvarchar(4000)

NULL

nvarchar(261)

NOT NULL

int

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(4000)

NOT NULL

nvarchar(max)

NULL

Foreign Key Diagram

entity_1_1_fk-uspgenerator.generatorusp_sqlusp

References

Object Reference Diagram - 1 1

entity_1_1_objectref-uspgenerator.generatorusp_sqlusp

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-uspgenerator.generatorusp_sqlusp

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-uspgenerator.generatorusp_sqlusp

Column Reference Diagram

entity_1_1_colref-uspgenerator.generatorusp_sqlusp

Indexes

idx_GeneratorUsp_SqlUsp__1

idx_GeneratorUsp_SqlUsp__2

idx_GeneratorUsp_SqlUsp__3

Column Details

_

AdocUspSteps

AdocUspSteps

nvarchar(max)

NOT NULL

has_logging

has_logging

tinyint

NOT NULL

MS_Description

MS_Description

nvarchar(4000)

NULL

RepoObject_guid

RepoObject_guid

uniqueidentifier

NULL

Referenced Columns

SqlStepList

SqlStepList

nvarchar(max)

NULL

SqlUsp

SqlUsp

nvarchar(max)

NOT NULL

SqlViewPersistenceUpdateCheck

SqlViewPersistenceUpdateCheck

nvarchar(max)

NULL

usp_Description

usp_Description

nvarchar(4000)

NULL

usp_fullname

usp_fullname

nvarchar(261)

NOT NULL

Description

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


usp_id

usp_id

int

NOT NULL

Referenced Columns

usp_name

usp_name

nvarchar(128)

NOT NULL

usp_schema

usp_schema

nvarchar(128)

NOT NULL

UspExamples

UspExamples

nvarchar(4000)

NOT NULL

UspParameters

UspParameters

nvarchar(max)

NULL

sql_modules_definition

uspgenerator.GeneratorUsp_SqlUsp - V script
/*
[SqlUsp] contains the final code for the usp, defined in
- [repo].[GeneratorUsp]
- [repo].[GeneratorUspParameter]
- [repo].[GeneratorUspStep]
*/
CREATE View uspgenerator.GeneratorUsp_SqlUsp
As
Select
    usp_id         = u.id
  , SqlUsp         = Concat (
                                'USE  ['
                              , dwhdb.dwh_database_name
                              , ']'
                              , Char ( 13 ) + Char ( 10 )
                              , 'GO'
                              , Char ( 13 ) + Char ( 10 )
                              , '/*'
                              , Char ( 13 ) + Char ( 10 )
                              , 'code of this procedure is managed in the dhw repository. Do not modify manually.'
                              , Char ( 13 ) + Char ( 10 )
                              , 'Use [uspgenerator].[GeneratorUsp], [uspgenerator].[GeneratorUspParameter], [uspgenerator].[GeneratorUspStep], [uspgenerator].[GeneratorUsp_SqlUsp]'
                              , Char ( 13 ) + Char ( 10 )
                              , '*/'
                              , Char ( 13 ) + Char ( 10 )
                              , 'CREATE OR ALTER PROCEDURE '
                              , u.usp_fullname
                              , Char ( 13 ) + Char ( 10 )
                              , ParameterList.ParameterList
                              , Case u.has_logging
                                    When 1
                                        Then
                                        Concat (
                                                   Iif(ParameterList.ParameterList <> ''
                                               , Char ( 13 ) + Char ( 10 ) + ',' + Char ( 13 ) + Char ( 10 )
                                               , '')
                                                 , '----keep the code between logging parameters and "START" unchanged!
---- parameters, used for logging; you don''t need to care about them, but you can use them, wenn calling from SSIS or in your workflow to log the context of the procedure call
  @execution_instance_guid UNIQUEIDENTIFIER = NULL --SSIS system variable ExecutionInstanceGUID could be used, any other unique guid is also fine. If NULL, then NEWID() is used to create one
, @ssis_execution_id BIGINT = NULL --only SSIS system variable ServerExecutionID should be used, or any other consistent number system, do not mix different number systems
, @sub_execution_id INT = NULL --in case you log some sub_executions, for example in SSIS loops or sub packages
, @parent_execution_log_id BIGINT = NULL --in case a sup procedure is called, the @current_execution_log_id of the parent procedure should be propagated here. It allowes call stack analyzing
'
                                               )
                                End --[u].[has_logging]
                              , '
AS
BEGIN
'
                              , Case u.has_logging
                                    When 1
                                        Then
                                        Concat (
                                                   'DECLARE
 --
   @current_execution_log_id BIGINT --this variable should be filled only once per procedure call, it contains the first logging call for the step ''start''.
 , @current_execution_guid UNIQUEIDENTIFIER = NEWID() --a unique guid for any procedure call. It should be propagated to sub procedures using "@parent_execution_log_id = @current_execution_log_id"
 , @source_object NVARCHAR(261) = NULL --use it like ''[schema].[object]'', this allows data flow vizualizatiuon (include square brackets)
 , @target_object NVARCHAR(261) = NULL --use it like ''[schema].[object]'', this allows data flow vizualizatiuon (include square brackets)
 , @proc_id INT = @@procid
 , @proc_schema_name NVARCHAR(128) = OBJECT_SCHEMA_NAME(@@procid) --schema ande name of the current procedure should be automatically logged
 , @proc_name NVARCHAR(128) = OBJECT_NAME(@@procid)               --schema ande name of the current procedure should be automatically logged
 , @event_info NVARCHAR(MAX)
 , @step_id INT = 0
 , @step_name NVARCHAR(1000) = NULL
 , @rows INT

--[event_info] get''s only the information about the "outer" calling process
--wenn the procedure calls sub procedures, the [event_info] will not change
SET @event_info = (
  SELECT TOP 1 [event_info]
  FROM sys.dm_exec_input_buffer(@@spid, CURRENT_REQUEST_ID())
  ORDER BY [event_info]
  )

IF @execution_instance_guid IS NULL
 SET @execution_instance_guid = NEWID();
--
--SET @rows = @@ROWCOUNT;
SET @step_id = @step_id + 1
SET @step_name = ''start''
SET @source_object = NULL
SET @target_object = NULL

EXEC logs.usp_ExecutionLog_insert
 --these parameters should be the same for all logging execution
   @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @parent_execution_log_id
 , @current_execution_guid = @current_execution_guid
 , @proc_id = @proc_id
 , @proc_schema_name = @proc_schema_name
 , @proc_name = @proc_name
 , @event_info = @event_info
 --the following parameters are individual for each call
 , @step_id = @step_id --@step_id should be incremented before each call
 , @step_name = @step_name --assign individual step names for each call
 --only the "start" step should return the log id into @current_execution_log_id
 --all other calls should not overwrite @current_execution_log_id
 , @execution_log_id = @current_execution_log_id OUTPUT
----you can log the content of your own parameters, do this only in the start-step
----data type is sql_variant
'
                                                 , ParameterList.ParameterListLogging
                                                 , '
--
PRINT '''
                                                 , u.usp_fullname
                                                 , '''
--keep the code between logging parameters and "START" unchanged!
--
----START
--
----- start here with your own code
--
'
                                               )
                                End --[u].[has_logging]
                              , StepList.StepList
                              , Case u.has_logging
                                    When 1
                                        Then
                                        '
--
--finish your own code here
--keep the code between "END" and the end of the procedure unchanged!
--
--END
--
--SET @rows = @@ROWCOUNT
SET @step_id = @step_id + 1
SET @step_name = ''end''
SET @source_object = NULL
SET @target_object = NULL

EXEC logs.usp_ExecutionLog_insert
   @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @parent_execution_log_id
 , @current_execution_guid = @current_execution_guid
 , @proc_id = @proc_id
 , @proc_schema_name = @proc_schema_name
 , @proc_name = @proc_name
 , @event_info = @event_info
 , @step_id = @step_id
 , @step_name = @step_name
 , @source_object = @source_object
 , @target_object = @target_object
'
                                End --[u].[has_logging]
                              , '
END

GO
'
                            )
  --2022-09-02 SqlViewPersistenceUpdateCheck
  , sqlview.SqlViewPersistenceUpdateCheck
  , AdocUspSteps   = Concat (
                                '.Steps in '
                              , u.usp_fullname
                              , Char ( 13 ) + Char ( 10 )
                              , '[cols="d,15a,d"]'
                              , Char ( 13 ) + Char ( 10 )
                              , '|==='
                              , Char ( 13 ) + Char ( 10 )
                              , '|'
                              , 'Number'
                              , '|'
                              , 'Name (Action, Source, Target)'
                              , '|'
                              , 'Parent'
                              , Char ( 13 ) + Char ( 10 )
                              , Char ( 13 ) + Char ( 10 )
                              , StepList.AdocStepList
                              , '|==='
                              , Char ( 13 ) + Char ( 10 )
                            )
  , MS_Description = u.usp_Description
  , u.has_logging
  , u.usp_Description
  , u.usp_fullname
  , u.usp_name
  , u.usp_schema
  , UspExamples    = IsNull ( u.usp_Examples, 'EXEC ' + u.usp_fullname )
  , UspParameters  = ParameterList.ParameterList
  , SqlStepList    = StepList.StepList
  , ro.RepoObject_guid
From
    uspgenerator.GeneratorUsp                                   As u
    Left Join
        uspgenerator.GeneratorUsp_ParameterList                 As ParameterList
            On
            ParameterList.usp_id = u.id

    Left Join
        uspgenerator.GeneratorUsp_StepList                      As StepList
            On
            StepList.usp_id = u.id

    Left Join
        repo.RepoObject                                         As ro
            On
            ro.RepoObject_fullname = u.usp_fullname
    --2022-09-02

    Left Join
        uspgenerator.GeneratorUsp_SqlViewPersistenceUpdateCheck As sqlview
            On
            sqlview.usp_id = u.id
    Cross Join config.ftv_dwh_database ()                       As dwhdb