Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

T-SQL Quick Reference - SQL Server

RSS
Modified on Fri, Aug 26, 2016, 9:43 AM by Administrator Categorized as Quick Reference, SQL Server

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

1 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

StageSyntax
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 UpdateUPDATE {table_name | view_name} SET {column=expression[,...]} WHERE CURRENT OF cursor_name
Positioned DeleteDELETE [FROM] {table_name | view_name} WHERE CURRENT OF cursor_name
Close 2 CLOSE CursorName
Deallocate 3 DEALLOCATE CursorName

1 See the Fetch Statuses table, below.
2 After closing a cursor, it can be re-opened by redeclaring it.
3 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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.