Semantic TagsLast updated: 7/24/2024

SQL+ Routine Tag

The routine tag is the only tag that is required and is placed at the beginning of a stored procedure or ad-hoc query. This tag signals to the builder that the routine is available for code generation. This tag also defines the select type, accepts a comment and author name, and has the option of changing the timeout to something other than the default. Each part of this tag is covered in detail below.

                
    --+SqlPlusRoutine
        --&SelectType=NonQuery or SingleRow or MultiRow or JSON or XML or MultiSet
        --&Comment=Comment
        --&Author=Author
        --&CommandTimeout=seconds
    --+SqlPlusRoutine 
                
           

*Note that the command timeout is optional.*

SelectType:

• NonQuery – the output of the call will not contain a result set.
• SingleRow – the procedure executes a SELECT that will return a maximum of one row. Used when selecting by a primary key. Since this will return a single row, the result set is mapped to a single instance of a (result object), not a list.
• MultiRow – the procedure executes a SELECT statement that will return an undetermined number of rows. The multi-row result is mapped into a list of (result object) not a single entity.
• JSON – the procedure executes a SELECT … FOR JSON PATH. The result string is mapped to a property - JsonResult.
• MultiSet – use this in combination with Query tags for services that return multiple result sets. See Multiple Result Sets and Query Tags for additional information.
• XML – the procedure executes a SELECT … FOR XML. The result string is mapped to a property XmlResult.

Comment: This provides a comment for your service, and aids in the transfer of knowledge between the creator of the service, and developers using the service.

Author: The creator of the SQL routine. This information provides other developers the means to quickly track and resolve issues about the SQL, you can also provide an email here if so desired.

CommandTimeout: Used to override the default timeout for commands. Enter the number of seconds for the allowed execution time.

SQL+ Parameter Validation Tags

Parameter validation tags are applied to parameters in stored procedures, or variables in ad-hoc queries. That validation is enforced in the service layer making your services the gatekeeper of pristine data. The following parameter validation tags are available:

            
    --+CreditCard
    --+Email
    --+MaxLength=MaximumLength
    --+MinLength=MinimumLength
    --+Phone
    --+PostalCode
    --+Range=MinimumValue,MaximumValue
    --+RegExPattern=RegularExpression
    --+Required
    --+StringLength=MinimumLength,MaximumLength
    --+Url
                
           

*Note that the use of these tags are optional.*

• CreditCard: Enforces credit card validation for the mapped property in the generated service.
• Email: Enforces email validation for the mapped property in the generated service.
• MaxLength: Enforces max length validation for the mapped property in the generated service.
• MinLength: Enforces min length validation for the mapped property in the generated service.
• Phone: Enforces liberal phone validation for the mapped property in the generated service. Also consider a regex for this case.
• PostalCode: Enforces liberal postal code validation for the mapped property in the generated service. Also consider a regex for this case.
• Range: Enforces range validation for the mapped property in the generated service.
  • MinimumValue: Specifies the minimum value allowed.
  • MaximumValue: Specifies the maximum value allowed.
• RegExPattern: Enforces regex validation for the mapped property in the generated service.
• Required: Enforces required (non-nullable) validation for the mapped property in the generated service.
• StringLength: Enforces min and max lengths for a string.
  • MinimumLength: Specifies the minimum length allowed.
  • MaximumLength: Specifies the maximum length allowed.
• Url: Enforces the format to be a fully qualified URL.

Validation Tag Supplemental Values

An additional feature available when applying validation tags is the ability to customize error messages. Simply add an ErrorMessage tag, or ErrorResource tag to make a custom error message available.

            
    --+CreditCard
    --&ErrorMessage=ErrorMessage

    --+CreditCard
    --&ErrorResource=ResourceType,ResourceName
             
           

Error Message: Provides a custom error message where "ErrorMessage" is the custom error.

Error Resource: Provides a custom error message residing in a resource file.

• ResourceType: Type of resource to utilize.
• ResourceName: Type name of the resource within the resource type.

SQL+ Parameter Display Tags

Parameter display tags are applied to parameters in stored procedures, or variables in ad-hoc queries. They are useful in UI frameworks that support data binding, as well as providing usefull information to users of your services.

            
    --+Comment=Comment
    --+Display=Name,Description
                
           

*Note that the use of these tags are optional.*

• Comment: The comment is applied to a parameter to create a comment for the generated property and is available to other developers using your service via IntelliSense.
• Display:
  • Name: The value to be displayed in place of the actual parameter name, or the name of the associated item in a resource file.
  • Description: This value is not currently used, but is required.

Display Tag Supplemental Values

An additional feature available when applying the display tag is the ability to link to resource files. When that is the case, the Name property represents the Name in the resource file.

            
    --&Resource=ResourceType
             
           

ResourceType: Specifies the resource file to use in tandem with the display property where the Display=Name points to the name of the item in the resource file.

SQL+ Parameter Direction (Mode) Tags

Stored Procedures

Parameters in stored procedures are inputs by default. If you designate a parameter with out, in the world of SQL it becomes an in/out parameter. In the context of SQL+ we treat input parameters as exclusively input, and out parameters as exclusively output. The parameter mode tags allow you to adjust this behavior as needed.

            
    --+InOut
                paramat
           

InOut: The parameter is available in both directions.

*Note the parameter must be designated as an out parameter in the stored procedure.

Ad-Hoc Queries

Parameters in ad-hoc queries are inputs by default. Since you can't designate a parameter in an ad-hoc query as out, this is always the case. To solve this limitation, SQL+ provides the following mode tags.

            
    --+InOut
    --+Output
                
           

InOut: Makes the parameter an in/out parameter.

Output: Makes the parameter exclusively an out parameter.

SQL+ Return Value Tags

The return tag is used to enumerate return values. Enumerating return values allows clear information about the outcome of a given service call to be passed back to developers using your service. The behavior varies slightly between stored procedures and ad-hoc queries.

Stored Procedures

            
    --+Return=EnumeratedValue,Description
    RETURN 1;
                
           

Ad-Hoc Queries

            
    --+Return=EnumeratedValue,Description
    SET @ReturnValue = 1;
                
           

Note that the parameter @ReturnValue must be designated as an Output.

SQL+ Multiple Result Query Tags

When procedures or ad-hoc queries return multiple result sets, each query is wrapped with query tags to indicate a name for the result set property as well as the select type that applies to that specific query.

Code snippet Routine-

            
    --+QueryStart=Name,SelectType 
    --+QueryEnd
                
           

Query Start

• Name: Specifies the name of the result object.
• SelectType: Specifies the select type - same behavior as the routine tag.

Query End: Marks the end of the query and should be placed after the entire select statement including any filter clauses or aggregates.

Note that the routine select type must be multiset to utilize query start and end tags.