usp generator

A generator for the formalized definition of usp (user defined procedures) which use Execution log.

If logging is to be used, then the code for logging is repeated several times. This can be simplified by separating the user code from the logging code. Logging can be switched on and off per procedure and per step.

usp generator procedures are defined in 3 tables:

A special case of the procedures created by the usp generator are the procedures for Persistence generator. These procedures are defined by the persistence settings in repo.RepoObject_persistence - U.

The final code of the generated procedures is available in uspgenerator.GeneratorUsp_SqlUsp.SqlUsp

If SSMS is used to retrieve the generated code, then the SSMS configuration should be checked and adjusted.

Challenges when using the usp generator

multiline data

The entries in uspgenerator.GeneratorUspStep.Statement are usually multi-line. But SSMS and Azure Data Studio do not have usable support for multi-line data entry.

One possibility is to use the DBeaver.

image dbeaver uspsteps

However, DBeaver does not currently allow new entries in tables with calculated columns. It is not possible to create new entries in uspgenerator.GeneratorUsp - U:

Other options would be:


If refactoring is used to rename objects or columns, then the code of the procedures can be changed with it. However, since the code of the procedures is ultimately created by the definitions in uspgenerator.GeneratorUspStep - U, these changes must primarily be entered there.