Example TSQL

Example: T-SQL Definition

This page documents the complete Crodox grammar definition for T-SQL (Transact-SQL), covering stored procedures, functions, triggers, DML/DDL statements, control flow, and more.

Key difference from Python: T-SQL uses BEGIN ... END for blocks, which maps perfectly to Crodox sub-bodies (BEGIN <---> END).

---

Full Definition

<~"FROM".sql~>

    <*comment_line*> -- -->> \n <*>
    <*comment_block*> /* -->> */ <*>

    <*()*> ( <---> ) <*>

    <:use:>
        USE <<name>> \n
    <:>

    <:go:>
        GO \n
    <:>

    <:declare:>
        DECLARE <<name>> <<'name'>> <| = -->> <| , <||> \n |> <||> <| , <||> \n |> |>
    <:>

    <:set:>
        SET <<'name'>> = -->> <| ; <||> \n |>
    <:>

    <:exec:>
        <| EXEC <||> EXECUTE |> <<'name'>> <? <<name>> <| , <||> |> ?> <| ; <||> \n |>
    <:>

    <:join:>
        <| INNER <||> LEFT <||> RIGHT <||> FULL <||> CROSS <||> |> JOIN <| <<'name'>> <||> <<name>> |> <| <| AS <||> |> <<name>> <||> |> ON -->> \n
    <:>

    <:select:>
        SELECT <| TOP <<name>> <||> |> <? <| <<'name'>> <||> <<name>> |> <| <| AS <||> |> <<name>> <||> |> <| , <||> |> ?>
        <| FROM <? <| <<'name'>> <||> <<name>> |> <| <| AS <||> |> <<name>> <||> |> <| , <||> |> ?> <||> |>
        <? <-{join}-> ?>
        <| WHERE -->> \n <||> |>
        <| GROUP BY <? <<'name'>> <| , <||> |> ?> <||> |>
        <| HAVING -->> \n <||> |>
        <| ORDER BY <? <<'name'>> <| ASC <||> DESC <||> |> <| , <||> |> ?> <||> |>
        <| ; <||> \n |>
    <:>

    <:insert:>
        INSERT INTO <<'name'>> <| ( <? <<'name'>> <| , <||> |> ?> ) <||> |>
        <| VALUES ( -->> ) <||> |>
        <| ; <||> \n |>
    <:>

    <:update:>
        UPDATE <<'name'>>
        SET <? <<'name'>> = -->> <| , <||> \n |> ?>
        <| WHERE -->> \n <||> |>
        <| ; <||> \n |>
    <:>

    <:delete:>
        DELETE FROM <<'name'>>
        <| WHERE -->> \n <||> |>
        <| ; <||> \n |>
    <:>

    <:if:>
        IF -->> BEGIN <---> END <| ELSE BEGIN <---> END <||> |>
    <:>

    <:while:>
        WHILE -->> BEGIN <---> END
    <:>

    <:begin_try:>
        BEGIN TRY <---> END TRY
        BEGIN CATCH <---> END CATCH
    <:>

    <:return:>
        RETURN <| -->> <| ; <||> \n |> <||> <| ; <||> \n |> |>
    <:>

    <:print:>
        PRINT -->> <| ; <||> \n |>
    <:>

    <:column_def:>
        <<name>> <<'name'>> <| ( <<name>> ) <||> |>
        <| NOT NULL <||> NULL <||> |>
        <| DEFAULT -->> \n <||> |>
        <| PRIMARY KEY <||> |>
        <| IDENTITY ( <<name>> , <<name>> ) <||> |>
        <| REFERENCES <<'name'>> ( <<'name'>> ) <||> |>
        <| , <||> |>
    <:>

    <:create_table:>
        CREATE TABLE <<name:up>> ( <? <-{column_def}-> ?> ) <| ; <||> \n |>
    <:>

    <:alter_table:>
        ALTER TABLE <<'name'>>
        <| ADD -->> <| ; <||> \n |>
        <||> DROP COLUMN <<'name'>> <| ; <||> \n |>
        <||> ALTER COLUMN -->> <| ; <||> \n |>
        |>
    <:>

    <:drop_table:>
        DROP TABLE <| IF EXISTS <||> |> <<'name'>> <| ; <||> \n |>
    <:>

    <:param:>
        <<name>> <<'name'>> <| = -->> <| , <||> \n |> <||> |> <| OUTPUT <||> |> <| , <||> |>
    <:>

    <:create_proc:>
        CREATE <| OR ALTER <||> |> PROCEDURE <<name:up>>
        <? <-{param}-> ?>
        AS BEGIN <---> END <| ; <||> \n |>
    <:>

    <:create_function:>
        CREATE <| OR ALTER <||> |> FUNCTION <<name:up>> (
        <? <-{param}-> ?>
        ) RETURNS <<'name'>> <| ( <<name>> ) <||> |>
        AS BEGIN <---> END <| ; <||> \n |>
    <:>

    <:create_view:>
        CREATE <| OR ALTER <||> |> VIEW <<name:up>>
        AS <| ; <||> \n |>
    <:>

    <:create_index:>
        CREATE <| UNIQUE <||> |> <| CLUSTERED <||> NONCLUSTERED <||> |> INDEX <<name:up>>
        ON <<'name'>> ( <? <<'name'>> <| ASC <||> DESC <||> |> <| , <||> |> ?> )
        <| ; <||> \n |>
    <:>

    <:create_trigger:>
        CREATE <| OR ALTER <||> |> TRIGGER <<name:up>>
        ON <<'name'>>
        <| AFTER <||> INSTEAD OF |> <? <| INSERT <||> UPDATE <||> DELETE |> <| , <||> |> ?>
        AS BEGIN <---> END <| ; <||> \n |>
    <:>

<~>

---

Object Breakdown

Hidden Objects

Object	Purpose	Notes
comment_line	Single-line comments (-- ...)	Jumps to \n to consume everything until end of line
comment_block	Block comments (/* ... */)	Jumps to */ closing delimiter
()	Parenthesised expressions / subqueries	Uses sub-body ( <---> ) so nested content is parsed recursively

Utility Statements

Object	Purpose	Notes
use	USE <database>	Switches active database
go	GO batch separator	Signals end of a batch to the SQL Server client
declare	DECLARE @var TYPE [= value]	Variable name and type captured; optional initialiser via or-statement
set	SET @var = expr	Variable assignment
exec	EXEC / EXECUTE	Procedure call with optional positional parameters (repeat)

DML Statements

Object	Purpose	Key Features
join	[INNER|LEFT|RIGHT|FULL|CROSS] JOIN ... ON	Separate object referenced by select; ON condition jumps to \n; also matches plain JOIN (empty type branch)
select	SELECT queries	Optional TOP, column list (repeat), FROM with comma-separated tables, repeated join references, WHERE, GROUP BY, HAVING, ORDER BY with ASC/DESC
insert	INSERT INTO	Table name, optional column list, optional VALUES row
update	UPDATE ... SET ... WHERE	Table name, repeated col = expr assignments, optional WHERE
delete	DELETE FROM ... WHERE	Table name, optional WHERE clause

Control Flow

Object	Purpose	Notes
if	IF ... BEGIN ... END [ELSE BEGIN ... END]	Condition captured via jump (-->>), body is a sub-body; optional ELSE branch with its own sub-body
while	WHILE ... BEGIN ... END	Loop condition captured via jump, body is a sub-body
begin_try	BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH	Two consecutive sub-bodies for try and catch blocks
return	RETURN [value]	Optional return expression
print	PRINT expr	Diagnostic output

DDL - Tables

Object	Purpose	Notes
column_def	Column definition inside CREATE TABLE	Captures name, type, optional length, NULL/NOT NULL, DEFAULT, PRIMARY KEY, IDENTITY, REFERENCES (foreign key)
create_table	CREATE TABLE	Table name captured with :up scope; columns via repeated column_def references
alter_table	ALTER TABLE	Three or-branches: ADD, DROP COLUMN, ALTER COLUMN
drop_table	DROP TABLE [IF EXISTS]	Optional IF EXISTS guard

DDL - Programmable Objects

Object	Purpose	Notes
param	Parameter definition	Used by create_proc, create_function, create_trigger. Captures @name TYPE [= default] [OUTPUT]
create_proc	CREATE [OR ALTER] PROCEDURE	Name with :up scope, repeated param references, AS BEGIN <---> END sub-body
create_function	CREATE [OR ALTER] FUNCTION	Name with :up scope, parenthesised params, RETURNS type, AS BEGIN <---> END sub-body
create_view	CREATE [OR ALTER] VIEW ... AS	Name with :up scope, followed by a SELECT (matched by the select object)
create_index	CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX	Name with :up scope, target table, repeated column list with sort direction
create_trigger	CREATE [OR ALTER] TRIGGER	Name with :up scope, target table, AFTER/INSTEAD OF with event list (INSERT, UPDATE, DELETE), AS BEGIN <---> END sub-body

---

Design Notes

Sub-bodies work naturally in T-SQL

Unlike Python (which uses indentation), T-SQL has explicit BEGIN ... END delimiters. This maps perfectly to Crodox sub-bodies:

IF -->> BEGIN <---> END

The parser knows exactly where the block starts and ends.

OR ALTER pattern

Many DDL statements support CREATE OR ALTER. This is modelled as an optional or-statement:

CREATE <| OR ALTER <||> |> PROCEDURE ...

Statement terminators

T-SQL statements can end with ; or a newline. This is captured with:

<| ; <||> \n |>

Column definitions use references

CREATE TABLE doesn't inline column definitions. Instead, it uses a column_def reference inside a repeat block:

CREATE TABLE <<name:up>> ( <? <-{column_def}-> ?> )

This keeps the grammar modular and allows the column definition to be reused.

---

Generate with the Template Generator

python crodox_template_generator.py tsql --output tsql.crodox

See Template Generator for details.