Write the database code

The trickiest part of this template is the column list. SchemaExplorer provides the columns too: the TableSchema object has a Columns collection you can loop through in code.

To run code in a template, put it between <% and %>. This code builds the column list, one column per line with commas in the right places:

<% For i As Integer = 0 To SourceTable.Columns.Count - 1 %>
        [<%= SourceTable.Columns(i).Name %>]<% If i < SourceTable.Columns.Count - 1 Then %>,<% End If %>
<% Next %>

Notice the three kinds of content here:

Syntax	What Generator does with it
<% ... %>	Runs the code.
<%= ... %>	Evaluates the expression and writes the result to the output.
Anything else	Copies it to the output as static content.

Keeping these straight can be one of the more confusing parts of working with CodeSmith Generator at first.

The square brackets around each name keep the SQL valid for columns whose names are reserved words or contain spaces.

The finished template

With all the pieces in place, here's the final template:

<%@ CodeTemplate Language="VB" TargetLanguage="T-SQL" Description="Creates a stored procedure that returns a table's rows as JSON." %>
<%@ Property Name="ExecuteAs" Type="ExecuteAsEnum" Category="Options" Default="CALLER" Description="The security context the procedure runs in." %>
<%@ Property Name="IsolationLevel" Type="IsolationLevelEnum" Category="Options" Default="READ_COMMITTED" Description="The transaction isolation level for the query." %>
<%@ Property Name="JsonMode" Type="JsonModeEnum" Category="Options" Default="PATH" Description="How FOR JSON shapes the output." %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="The table the procedure returns." %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
CREATE OR ALTER PROCEDURE <%= SourceTable.Owner %>.Get<%= SourceTable.Name %>Json
WITH EXECUTE AS <%= ExecuteAs %>
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL <%= GetIsolationLevel(IsolationLevel) %>;

    SELECT
<% For i As Integer = 0 To SourceTable.Columns.Count - 1 %>
        [<%= SourceTable.Columns(i).Name %>]<% If i < SourceTable.Columns.Count - 1 Then %>,<% End If %>
<% Next %>
    FROM
        [<%= SourceTable.Owner %>].[<%= SourceTable.Name %>]
    FOR JSON <%= JsonMode %>, ROOT('<%= SourceTable.Name %>');
END
GO
<script runat="template">
Public Enum ExecuteAsEnum
    CALLER
    OWNER
    SELF
End Enum

Public Enum IsolationLevelEnum
    READ_UNCOMMITTED
    READ_COMMITTED
    REPEATABLE_READ
    SNAPSHOT
    SERIALIZABLE
End Enum

Public Enum JsonModeEnum
    PATH
    AUTO
End Enum

Public Function GetIsolationLevel(ByVal Level As IsolationLevelEnum) As String
    Select Case Level
        Case IsolationLevelEnum.READ_UNCOMMITTED
            Return "READ UNCOMMITTED"
        Case IsolationLevelEnum.REPEATABLE_READ
            Return "REPEATABLE READ"
        Case IsolationLevelEnum.SNAPSHOT
            Return "SNAPSHOT"
        Case IsolationLevelEnum.SERIALIZABLE
            Return "SERIALIZABLE"
        Case Else
            Return "READ COMMITTED"
    End Select
End Function
</script>

To learn more about the objects you used, see Using SchemaExplorer.

Next: Test the final result