T-SQL Quick Reference - SQL Server

Table of Contents [Hide/Show]

   Identifiers
   Object References
   Operators
   Built-In Functions
   Stored Procedures
      Syntax
      Logic Flow Control
      Reserved Return Statuses
      Calling a Stored Procedure
   Raising Exceptions
   Transaction Handling
   Cursors
      Logic Flow
      Fetch Statuses

Identifiers¶

May consist of letters, numbers, spaces, and the following symbols _ (underscore), @ (AT symbol), # (pound sign)
May have at most 128 characters
If contain spaces, must be enclosed in brackets. May also be enclosed in double quotes if SET QUOTED_IDENTIFIER is ON.
Standard prefixes

Prefix	Usage
`@`	Local variable or parameter
`@@`	Global variable
`#`	Temporary table or procedure
`##`	Global temporary object

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_USER`¹, `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¶

RAISERROR ({msgid | msgstring}{, severity, state} [, argument [, ... N]] ] WITH option]

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¶

Note: Unlike local variables, cursor names should NOT begin with an "@".

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 ¹	`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 ²	`CLOSE CursorName`
Deallocate ³	`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