Database-StoredProcedure-Config.md

'StoredProcedure' object (database-driven)

The code generation for an StoredProcedure is primarily driven by the Type property. This encourages (enforces) a consistent implementation for the standardised CRUD (Create, Read, Update and Delete) actions, as well as supporting Upsert, Merge and ad-hoc queries as required.

The valid Type values are as follows:

• Get - indicates a get (read) returning a single row value. The primary key is automatically added as a Parameter.
• GetAll - indicates an ad-hoc query/get (read) returning one or more rows (collection). No Parameters are automatically added.
• Create - indicates the creation of a row. All columns are added as Parameters.
• Update - indicates the updating of a row. All columns are added as Parameters.
• Upsert - indicates the upserting (create or update) of a row. All columns are added as Parameters.
• Delete - indicates the deleting of a row. The primary key is automatically added as a Parameter.
• Merge - indicates the merging (create, update or delete) of one or more rows (collection) through the use of a Table-Valued Parameter (TVP) Type parameter.

Example

A YAML example is as follows:

tables:
- { name: Table, schema: Test, create: true, update: true, upsert: true, delete: true, merge: true, udt: true, getAll: true, getAllOrderBy: [ Name Des ], excludeColumns: [ Other ], permission: TestSec,
    storedProcedures: [
      { name: GetByArgs, type: GetColl, excludeColumns: [ Count ],
        parameters: [
          { name: Name, nullable: true, operator: LIKE },
          { name: MinCount, operator: GE, column: Count },
          { name: MaxCount, operator: LE, column: Count, nullable: true }
        ]
      },
      { name: Get, type: Get, withHints: NOLOCK,
        execute: [
          { statement: EXEC Demo.Before, location: Before },
          { statement: EXEC Demo.After }
        ]
      },
      { name: Update, type: Update }
    ]
  }

Property categories

The StoredProcedure object supports a number of properties that control the generated code output. These properties are separated into a series of logical categories.

Category	Description
Key	Provides the key configuration.
Merge	Provides Merge configuration (where Type is Merge).
Additional	Provides additional ad-hoc configuration.
Auth	Provides the Authorization configuration.
Columns	Provides the Columns configuration.
Collections	Provides related child (hierarchical) configuration.

The properties with a bold name are those that are more typically used (considered more important).

Key

Provides the key configuration.

Property	Description
name	The name of the StoredProcedure; generally the verb/action, i.e. Get, Update, etc. [Mandatory] † See StoredProcedureName for the actual name used in the database.
type	The stored procedure operation type. Valid options are: Get, GetColl, Create, Update, Upsert, Delete, Merge. † Defaults to GetColl.
paging	Indicates whether standardized paging support should be added. † This only applies where the stored procedure operation Type is GetColl.
storedProcedureName	The StoredProcedure name in the database. † Defaults to sp + Table.Name + Name; e.g. spTableName or spPersonGet.
replace	Indicates whether the existing StoredProcedure database object should be replaced/altered or whether the object is dropped and recreated. † Defaults to CodeGeneration.Replace.

Merge

Provides Merge configuration (where Type is Merge).

Property	Description
mergeOverrideIdentityColumns	The list of Column names to be used in the Merge statement to determine whether to insert, update or delete. † This is used to override the default behaviour of using the primary key column(s).

Additional

Provides additional ad-hoc configuration.

Property	Description
reselectStatement	The SQL statement to perform the reselect after a Create, Update or Upsert stored procedure operation Type. † Defaults to [{{Table.Schema}}].[sp{{Table.Name}}Get] passing the primary key column(s).
intoTempTable	Indicates whether to select into a #TempTable to allow other statements access to the selected data. † A Select * from #TempTable is also performed (code-generated) where the stored procedure operation Type is GetColl.
withHints	the table hints using the SQL Server WITH() statement; the value specified will be used as-is; e.g. NOLOCK will result in WITH(NOLOCK).
collectionType	The collection type. Valid options are: JSON, UDT. † Values are JSON being a JSON array (preferred) or UDT for a User-Defined Type (legacy). Defaults to Table.CollectionType.

Auth

Provides the Authorization configuration.

Property	Description
permission	The name of the StoredProcedure in the database.
orgUnitImmutable	Indicates whether the OrgUnitId column is considered immutable, in that it can not be changed once set. † Defaults to Table.OrgUnitImmutable.

Columns

Provides the Columns configuration.

Property	Description
includeColumns	The list of Column names to be included in the underlying generated settable output (further filters Table.IncludeColumns). † Where not specified this indicates that all Columns are to be included. Only filters the columns where Type is Get, GetColl, Create, Update or Upsert.
excludeColumns	The list of Column names to be excluded from the underlying generated settable output (further filters Table.ExcludeColumns). † Where not specified this indicates no Columns are to be excluded. Only filters the columns where Type is Get, GetColl, Create, Update or Upsert.

Collections

Provides related child (hierarchical) configuration.

Property	Description
parameters	The corresponding Parameter collection.
where	The corresponding Where collection.
orderby	The corresponding OrderBy collection.
execute	The corresponding Execute collection.