Object References¶

[[[server.]database.]owner.]object

where server defaults to the local server, database defaults to the current database, and owner defaults to the current user.

Operators¶

Operator	Usage
+ - * /	Arithmetic
+	String concatentation (Note: An empty string gets converted to a single blank space.)
POWER function	Exponentiation
< >	Less than; greater than
<= >=	Less than or equal; greater than or equal
!= or <>	Not equal
%	Modulo

Built-In Functions¶

Group	Functions
Aggregate	AVG, COUNT, MAX, MIN, SUM Note: Nulls are ignored for SUM and AVG
Data Type Conversion	CAST, CONVERT
Date/Time	DATEADD, DATEDIFF, DATENAME, DATEPART, DAY, GETDATE, GETUTCDATE, MONTH, YEAR
Math	ABS, CEILING, EXP, FLOOR, PI, POWER, RAND, ROUND, SIGN, SQRT
Metadata	COL_LENGTH, COL_NAME, COLUMNPROPERTY, OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY
Security	SYSTEM_USER1, IS_MEMBER, IS_SRVROLEMEMBER, SUSER_SID, SUSER_SNAME, USER, USER_ID
String	ASCII, CHAR, LEFT, LEN, LOWER, LTRIM, RIGHT, RTRIM, SUBSTRING, UPPER
System	CASE, IDENT_INCR, IDENT_SEED, ISNULL, NEWID, NULLIF
Global Variables	@@ERROR, @@CONNECTION, @@VERSION, @@TRANCOUNT, @@ROWCOUNT, @@IDENTITY, @@FETCH_STATUS, @@NESTLEVEL

¹ Use the SYSTEM_USER function without parentheses.

Stored Procedures¶

Syntax¶

CREATE PROC[EDURE] procname [;number]
[{@param datatype} [= default][ OUTPUT] 
[WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}]
AS SqlStatements 

Logic Flow Control¶

Keywords	Usage
BEGIN/END	Statement grouping (blocks)
GOTO	Unconditional branching
IF/ELSE	Conditional branching. Frequently used with BEGIN/END
RETURN [(ReturnStatus)]	Establishes procedure's return value and ends it without error.
WAITFOR	Pauses processing for a specified duration (up to 24 hours) or until a specified time of day
WHILE	Conditional looping. Frequently used with BEGIN/END
CASE WHEN a THEN b WHEN c THEN d END	Conditional branching. BEGIN/END not needed.

Reserved Return Statuses¶

Status	Meaning
0	Procedure executed successfully
-1	Object missing
-2	Data type error
-3	Deadlock victim
-4	Permission error
-5	Syntax error
-6	Miscellaneous error
-7	Resource error (e.g., out of space)
-8	Non-fatal internal error
-9	System limit reached
-10, -11	Fatal internal inconsistency
-12	Table or index is corrupt
-13	Database is corrupt
-14	Hardware error
-15 to -99	Reserved for future use

Calling a Stored Procedure¶

Execute Statement

EXEC[UTE] [@ReturnStatus = ] {procname[;number] | @ProcNameVar }
[[@parameter = ]{value | @variable [OUTPUT] | [DEFAULT]] [WITH RECOMPILE]

Within an Insert Statement
Inserts data from result set into the given table

INSERT INTO TableName EXECUTE StoredProcName 

Raising Exceptions¶

Transaction Handling¶

Statement	Usage
BEGIN TRANSACTION	Marks the beginning of a transaction
COMMIT TRANSACTION	Permanently writes all data changes executed since the last pending BEGIN TRANSACTION
ROLLBACK TRANSACTION	Cancels all data changes executed since the last pending BEGIN TRANSACTION

Cursors¶

Logic Flow¶

Stage	Syntax
Declare	DECLARE CursorName CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] FOR SelectStatement [FOR {READONLY | UPDATE (OF ColumnList)}]
Open	OPEN CursorName
Read 1	FETCH {FIRST | NEXT} FROM CursorName INTO VariableList
Positioned Update	UPDATE {table_name | view_name} SET {column=expression[,...]} WHERE CURRENT OF cursor_name
Positioned Delete	DELETE [FROM] {table_name | view_name} WHERE CURRENT OF cursor_name
Close 2	CLOSE CursorName
Deallocate 3	DEALLOCATE CursorName

¹ See the Fetch Statuses table, below.
² After closing a cursor, it can be re-opened by redeclaring it.
³ After deallocating a cursor, it CANNOT be re-opened.

Fetch Statuses¶

@@FETCH_STATUS	Description
0	FETCH statement was successful
-1	FETCH statement failed or the row was beyond the result set
-2	Row fetched is missing