uspgenerator.GeneratorUsp_ParameterList - V

type: V ( view ), modify_date: 2021-08-17 20:19:31

RepoObject_guid: E18F291C-9D61-EB11-84DC-A81E8446D5B0

Description

Examples

Entity Diagram

entity-uspgenerator.generatorusp_parameterlist

Columns

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

1

int

NOT NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

Foreign Key Diagram

entity_1_1_fk-uspgenerator.generatorusp_parameterlist

References

Object Reference Diagram - 1 1

entity_1_1_objectref-uspgenerator.generatorusp_parameterlist

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-uspgenerator.generatorusp_parameterlist

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-uspgenerator.generatorusp_parameterlist

Column Reference Diagram

entity_1_1_colref-uspgenerator.generatorusp_parameterlist

Indexes

PK_GeneratorUsp_ParameterList

Column Details

_

usp_id

1

usp_id

int

NOT NULL

ParameterList

ParameterList

nvarchar(max)

NULL

ParameterListLogging

ParameterListLogging

nvarchar(max)

NULL

sql_modules_definition

uspgenerator.GeneratorUsp_ParameterList - V script
/*
List of parameters without trailing comma
*/
CREATE View uspgenerator.GeneratorUsp_ParameterList
As
Select
    up.usp_id
  , ParameterList        = String_Agg (
                                          Concat (
                                                     Cast('@' As NVarchar(Max))
                                                   , up.Name
                                                   , ' '
                                                   , up.UserTypeFullname
                                                   , Iif(up.has_DefaultValue = 1
                                                  , Concat ( ' = ', IsNull ( up.DefaultValue, 'NULL' ))
                                                  , Null)
                                                   , Iif(up.is_out = 1, ' OUTPUT', Null)
                                                   , Iif(Not up.Description Is Null, ' /* ' + up.Description + ' */', Null)
                                                 )
                                        , Char ( 13 ) + Char ( 10 ) + ','
                                      ) Within Group(Order By
                                                         up.Number)
  , ParameterListLogging = String_Agg (
                                          Concat (
                                                     Cast(' , @parameter_' As NVarchar(Max))
                                                   , Right(Concat ( '0', up.RowNumber_PerUsp ), 2)
                                                   , Cast(' = @' As NVarchar(Max))
                                                   , up.Name
                                                 )
                                        , Char ( 13 ) + Char ( 10 )
                                      ) Within Group(Order By
                                                         up.Number)
From
(
    Select
        par.usp_id
      , par.Number
      , par.Name
      , par.UserTypeFullname
      , par.is_inactive
      , par.is_out
      , par.has_DefaultValue
      , par.DefaultValue
      , par.Description
      , RowNumber_PerUsp = Row_Number () Over ( Partition By par.usp_id Order By par.Number )
    From
        uspgenerator.GeneratorUspParameter As par
) As up
Where
    up.is_inactive = 0
Group By
    up.usp_id