Labels

Label a block of code:

 <<myLabel>>
 BEGIN
    //statements
 END;

An exit command can be labeled to tell which block to exit. Exit command must be nested within that block.

 <<myLabel>>
 BEGIN
    LOOP
        IF x > 10 THEN
            EXIT myLabel; //execution of loop ends here
        END IF;
    END LOOP;
 END;
 //execution picks up outside "myLabel" block

A continue command can be labeled to tell which loop to continue the next iteration of. Continue command must be nested within that loop.

 <<labelA>>
 LOOP
    //execution picks up on next iteration of "labelA" loop 
    <<labelB>>
    LOOP
        IF x > 10 THEN
            CONTINUE labelA; //execution of loop ends here
        END IF;
    END LOOP;
 END LOOP;


Variables

Declaration:

x integer;
y varchar;

Assignment:

x := 0;
y := 'text';
Tables

Temp


CREATE TEMPORARY TABLE myTable (
    id int not null
    //and other columns
);
Insert

Insert entire result set:

INSERT INTO myTable (id) 
SELECT customerId 
FROM customerTable
WHERE Age > 65;
Conditionals

If


IF x > 10 THEN
    //statements
END IF;


IF x > 10 THEN
    //statements
ELSIF
    //statements
ELSE
    //statements
END IF;

Case


CASE
    WHEN x == 0 THEN
        //statements
    WHEN x == 1, x == 2 THEN
        //statements
    ELSE
        //statements
END CASE;


CASE x
    WHEN 0 THEN
        //statements
    WHEN 1,2 THEN
        //statements
    ELSE
        //statements
END CASE;

Loops

Loop


LOOP
    //statements
END LOOP;

Exit loop on condition:

LOOP
    EXIT WHEN x > 10;
END LOOP;

Continue next iteration of loop:

LOOP
    CONTINUE;
END LOOP;

Continue next iteration of loop on condition:

LOOP
    CONTINUE WHEN x > 10;
END LOOP;

While


WHILE x < 10 LOOP
    //statements
END LOOP;

For Integer

Iterate through list of integers. 1..10 provides 1,2,3,4,5,6,7,8,9,10.

FOR i IN 1..10 LOOP
    //statements
END LOOP;

Specify a step other than the default of 1. This loops through 1,3,5,7,9.

FOR i IN 1..10 BY 2 LOOP
    //statements
END LOOP;

Reverse the order. This loops through 10,9,8,7,6,5,4,3,2,1.

FOR i IN REVERSE 1..10 LOOP
    //statements
END LOOP;

For Query Result


FOR record IN SELECT id, lastName FROM myTable LOOP
    //record.id
    //record.lastName
END LOOP;


Functions


CREATE TABLE myTable (id INT NOT NULL);
INSERT INTO myTable VALUES (1);
INSERT INTO myTable VALUES (2);
INSERT INTO myTable VALUES (3);

CREATE OR REPLACE FUNCTION myFunction() RETURNS SETOF myTable AS
$BODY$ //any label can be put inside the $$ characters
    DECLARE
        result myTable%rowtype;
    BEGIN
        FOR result IN SELECT * FROM myTable ORDER BY id ASC
        LOOP
            IF result.id > 2 THEN
                EXIT;
            END IF;
            RETURN NEXT result;
        END LOOP;
        RETURN;
    END
$BODY$
LANGUAGE 'plpgsql';

SELECT * FROM myFunction();

Collation

Collation can be set at the database level, can be overridden at the table level (?maybe?), and can be overridden at the column level.

Collation affects how text data is interpreted, how text is sorted, case-sensitivity rules, etc.

You cannot compare text data that is using different collations.

Database

To see what collation a database is using:
1) Open SSMS
2) Right-click on the database > Properties > Options tab > Collation is at the top

Or in T-SQL:

--for the server
SELECT CONVERT (varchar(256), SERVERPROPERTY('collation'));

--for the database
SELECT name, collation_name FROM sys.databases; 

--for the column
SELECT name, collation_name FROM sys.columns WHERE name = N'<column name>';

To see all supported collations:

SELECT name, description FROM sys.fn_helpcollations();