The scenario: a stored procedure that returns JSON
This tutorial generates a stored procedure that returns all the rows of a table as a JSON document, ready for a web API or a front end to pass straight through. It uses CREATE OR ALTER PROCEDURE and FOR JSON, which work in SQL Server 2016 SP1 and later and in Azure SQL Database.
A procedure like this has several options with a fixed list of valid values (the security context it runs in, the transaction isolation level, how FOR JSON shapes the output) plus details that depend on the table: its schema and name, and every column. Writing one by hand is easy; writing one for each of fifty tables, and keeping them right as the tables change, is tedious and error-prone. That's an ideal opening for code generation. Instead of working through the details every time, you work them out once and capture what you know in a CodeSmith Generator template.
To begin, you need an example of the SQL you want to create.