uspgenerator.GeneratorUsp_ParameterList - V
type: V ( view ), modify_date: 2021-08-17 20:19:31
RepoObject_guid: E18F291C-9D61-EB11-84DC-A81E8446D5B0
Columns
PK | Column Name | Data Type | NULL? | ID |
---|---|---|---|---|
1 |
|
|
||
|
|
|||
|
|
Indexes
PK_GeneratorUsp_ParameterList
-
IndexSemanticGroup: no_group
-
[column-usp_id]; int
-
-
PK, Unique, Real: 1, 1, 0
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
sql