Routines or QueriesLast updated: 6/2/2023
SQL+ will work with any database routine or ad-hoc SQL that you apply Semantic Tags to.
When using database routines, add the semantic tags to the routine body. When you run the SQL+ code generation utility, it will display these routines in the UI. By default, these routines will use the schema name as the namespace. You can assign a namespace at the schema level and it will apply to all routines in that schema. Alternatively, you can assign individual routines to a schema if you need a more granular structure. SQL+ will create a folder structure based on the namespace assignments.
In the following illustration, there are two schemas, dbo and pub. The dbo schema has been assigned to the Default namespace, and the pub schema has been assigned to the Public namespace.
The folder structure below reflects how those assignments are applied in the generated services.
In SQL+, ad-hoc queries are .sql files that have been semantically tagged. Unlike routines, when using ad-hoc queries, it is the folder structure that dictates namespace assignments, where each subdirectory of the Queries folder becomes a namespace, and the name of the file is utilized for the name of the generated service.
In the UI, you can choose which files you want to include in the build. In this case we chose all files in Default, and Public folders (namepaces).
The folder structure below reflects how the Queries folder structure is utilized in the generated services.
Which is Better?
The real question that needs to be asked is which is better for you?
If performance is your thing, stored procedures are the way to go. If you like self contained services that are easier to deploy, ad-hoc queries are a better choice.
You should discuss this with your team, and come up with a strategy that suits the way you work.