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 |>
    <:>

    <:select:>
        SELECT <| TOP <<name>> <||> |> <? <| <<'name'>> <||> <<name>> |> <| <| AS <||> |> <<name>> <||> |> <| , <||> |> ?>
        <| FROM <? <| <<'name'>> <||> <<name>> |> <| <| AS <||> |> <<name>> <||> |>
            <| <| INNER <||> LEFT <||> RIGHT <||> FULL <||> CROSS |> JOIN <| <<'name'>> <||> <<name>> |> <| <| AS <||> |> <<name>> <||> |> ON -->> <| , <||> |> <||> |>
        ?> <||> |>
        <| WHERE -->> <||> |>
        <| GROUP BY <? <<'name'>> <| , <||> |> ?> <||> |>
        <| HAVING -->> <||> |>
        <| ORDER BY <? <<'name'>> <| ASC <||> DESC <||> |> <| , <||> |> ?> <||> |>
        <| ; <||> \n |>
    <:>

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

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

    <:delete:>
        DELETE FROM <<'name'>>
        <| WHERE -->> <||> |>
        <| ; <||> \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 -->> <||> |>
        <| 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'>> <| = -->> <||> |> <| 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
select	SELECT queries	Optional TOP, column list (repeat), FROM with optional JOIN (5 join types), 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.