Set up enumerated properties

Several values the template needs from the user have only a few valid choices. For example, EXECUTE AS accepts only CALLER, OWNER or SELF; anything else is a T-SQL error. Instead of free-form input (and the risk of an invalid value), offer a list of just the valid choices with an enumerated property.

Add an enumeration

To set up an enumerated property, you define a type that allows only the values you want: an enumeration.

1. Move to the end of the template and start typing <script. The Template Editor offers to complete a script block.

2. Press Tab, or click the suggestion, to insert the script block with its closing tag.

3. Inside the script block, define the enumeration:

   <script runat="template">
   Public Enum ExecuteAsEnum
       CALLER
       OWNER
       SELF
   End Enum
   </script>
   

Declare the property

Add a Property directive that uses the new type:

<%@ Property Name="ExecuteAs" Type="ExecuteAsEnum" Category="Options" Default="CALLER" Description="The security context the procedure runs in." %>

Writing an enumeration value to the output writes its name, so <%= ExecuteAs %> writes CALLER, OWNER or SELF: exactly the T-SQL keyword. The same goes for the FOR JSON mode, with an enumeration of PATH and AUTO.

Translate values that aren't valid names

Isolation levels are different: READ COMMITTED has a space, and an enumeration value can't. Name the values with underscores instead, and add a helper function inside the script block that returns the T-SQL for each one:

Public Enum IsolationLevelEnum
    READ_UNCOMMITTED
    READ_COMMITTED
    REPEATABLE_READ
    SNAPSHOT
    SERIALIZABLE
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

Now <%= GetIsolationLevel(IsolationLevel) %> writes the user's choice anywhere in the template.

The template so far

With the three enumerations, their properties and the helper function, the template looks like this:

<%@ 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." %>
CREATE OR ALTER PROCEDURE Person.GetAddressTypeJson
WITH EXECUTE AS <%= ExecuteAs %>
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL <%= GetIsolationLevel(IsolationLevel) %>;

    SELECT
        [AddressTypeID],
        [Name],
        [rowguid],
        [ModifiedDate]
    FROM
        [Person].[AddressType]
    FOR JSON <%= JsonMode %>, ROOT('AddressType');
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>

Save the template. One thing is still missing: a connection to the database. You add that next.

Next: Set up a SQL property