VistaDB 5
Gibraltar VistaDB > Developer's Guide > SQL Reference > SQL Reference - Control of Flow Statements
SQL Reference - Control of Flow Statements

BEGIN / END

Example Title
Copy Code
BEGIN
{
sql_statement | statement_block
}
END


IF / ELSE

IF / ELSE
Copy Code
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]

WHILE

WHILE
Copy Code
WHILE Boolean_expression
[{ sql_statement | statement_block } ]
[BREAK;] - Exit from WHILE loop before Boolean_expression is false
[CONTINUE;] - Stop processing WHILE loop and return to beginning of WHILE loop

Try / Catch operation - error handling concepts for SQL script code.

RAISERROR

RAISERROR is used to introduce an error condition through application logic to a SQL command.

SET OPTIMIZATION { ON | OFF }

SET CHECK VIEW { ON | OFF }

SET @VARIABLE_NAME = EXPRESSION

Topics below this line are from the BNF syntax but do not have examples in the help at this time.


CREATE { INMEMORY DATABASE } | { DATABASE file_name [ IN ISOLATED STORAGE ] } [, PASSWORD password] [, PAGE SIZE page_size] [, LCID lcid] [, CASE SENSITIVE case_sensitive] [, DESCRIPTION description]

file_name - database file name. This can be only string qualifier.
password - password for database, by default is empty.
page_size - database page size.
lcid - locale id for database, by default system locale.
case_sensitive - True (by default) for case sensitive database, else False
description - database description

Examples:

CREATE DATABASE Test.vdb4

CREATE TABLE table_name [DESCRIPTION description]
(
<column definition> [,�n]
[, <table constraint> [,�n] ]
)

<column_definition> ::=
column_name <data_type> [ NULL | NOT NULL ]
[READ ONLY | NOT READ ONLY]
[ENCRYPTED | NOT ENCRYPTED]
[PACKED | NOT PACKED]
[ DEFAULT default_constant | IDENTITY [ ( seed, increment ) ] ]
[CAPTION caption]
[DESCRIPTION description]
[<column constraint> [,�n] ]

<data_type> ::=
type_name [ ( width ) ] [ CODE PAGE code_page ]

<column constraint> ::=
[CONSTRAINT constraint_name ]
{ PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ ASC | DESC ]
| [ FOREIGN KEY ]
REFERENCES referenced_table_name ( ref_column )
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{ PRIMARY KEY | UNIQUE }[ CLUSTERED | NONCLUSTERED ] (column [ ASC | DESC ] [ ,...n ] )
| FOREIGN KEY ( column [ ,...n ] )
REFERENCES referenced_table_name ( ref_column [ ,...n ] )
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
| CHECK [ ON DELETE ] [ ON INSERT ] [ ON UPDATE ] ( logical_expression )
}

ALTER TABLE table_name
{
DESCRIPTION description
| ALTER COLUMN column_name
[ <data_type> ] [ NULL | NOT NULL ]
[READ ONLY | NOT READ ONLY]
[ENCRYPTED | NOT ENCRYPTED]
[PACKED | NOT PACKED]
[ DEFAULT default_constant | IDENTITY [ ( seed, increment ) ] | DROP IDENTITY ]
[CAPTION caption]
[DESCRIPTION description]
| ADD
{
<column_definition>
| < table_constraint >
} [ ,� ]
| DROP
{
[ CONSTRAINT ] constraint_name
| COLUMN column_name
} [ ,� ]
}


DROP TABLE table_name [ ,...n ]

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON table_name ( column [ ASC | DESC ] [ ,...n ] )

DROP INDEX index_name ON table_name

ALTER INDEX { index_name | ALL } ON table_name REBUILD

CREATE VIEW view_name [ DESCRIPTION description ] [ ( column [, � n] ) ] AS select_statement

ALTER VIEW view_name [ DESCRIPTION description ] [ ( column [, � n] ) ] AS select_statement

DROP VIEW view_name [ ,...n ]

BEGIN { TRANS | TRANSACTION }
COMMIT { TRANS | TRANSACTION }
ROLLBACK { TRANS | TRANSACTION }

CREATE ASSEMBLY assembly_name [ DESCRIPTION description ] FROM assembly_filename

ALTER ASSEMBLY assembly_name [ DESCRIPTION description ] FROM assembly_filename

DROP ASSEMBLY assembly_name [ ,�n ]


CREATE { PROC | PROCEDURE } procedure_name [ DESCRIPTION description ] AS EXTERNAL NAME assembly_name.class_name.method_name

DROP { PROC | PROCEDURE } procedure_name [ ,�n ]


{ EXEC | EXECUTE } procedure_name [ ,�n ]

DECLARE { @variable_name [ AS ] data_type } [ ,�n ]


SELECT [ ALL | DISTINCT ] [ TOP row_count ] <select_list>

<select_list> ::=
{
*
| { table_name | view_name | table_alias }.{ * | column_name }
| { column_name | expression }
[ [ AS ] column_alias ]
} [ ,�n ]


[ FROM { <table_source> } [ ,�n ] ]

<table_source> ::=
{
table_or_view_name [ [ AS ] table_alias ]
| ( select_statement ) [ [ AS ] table_alias ]
| function [ [ AS ] table_alias ]
| <joined_table>
}

<joined_table> ::=
{
<table_source> <join_type> <table_source> ON search_condition
| <table_source> CROSS JOIN <table_source>
}

<join_type> ::=
[ { INNER | { { LEFT | RIGHT } [ OUTER ] } } ]
JOIN


[ WHERE search_conditon ]


[ GROUP BY group_by_expression [ ,�n ] ]


[ HAVING search_expression ]


select_statement
UNION [ ALL ]
select_statement
[ UNION [ ALL ] select_statement ] [ ,�n ]


[ ORDER BY
{
column_name | column_alias | column_number
[ ASC | DESC ]
} [ ,�n ]
]


INSERT [ INTO ] table_or_view_name
{
[ ( column_list ) ]
{
VALUES ( { NULL | expression } [ ,�n ] )
| select_statement
}
}
| DEFAULT VALUES


UPDATE table_or_view_name
SET { column_name = expression } [ ,�n ]
[ <from_clause> ]
[ <where_clause> ]


DELETE [ FROM ] table_or_view_name
[ <from_clause> ]
[ <where_clause> ]



See Also