MSDN Library

Servers and Enterprise Development

SQL Server

SQL Server 2008

Product Documentation

SQL Server 2008 Books Online

Database Engine

Technical Reference

Transact-SQL Reference

Transact-SQL Syntax Conventions (Transact-SQL)

Tutorial: Writing Transact-SQL Statements

+ (Add) (Transact-SQL)

+ (Unary Plus) (Transact-SQL)

+ (String Concatenation) (Transact-SQL)

+= (Add EQUALS) (Transact-SQL)

+= (String Concatenation) (Transact-SQL)

- (Negative) (Transact-SQL)

- (Subtract) (Transact-SQL)

-= (Subtract EQUALS) (Transact-SQL)

* (Multiply) (Transact-SQL)

*= (Multiply EQUALS) (Transact-SQL)

/ (Divide) (Transact-SQL)

/= (Divide EQUALS) (Transact-SQL)

\ (Backslash) (Transact-SQL)

% (Modulo) (Transact-SQL)

%= (Modulo EQUALS) (Transact-SQL)

% (Wildcard - Character(s) to Match) (Transact-SQL)

& (Bitwise AND) (Transact-SQL)

&= (Bitwise AND EQUALS) (Transact-SQL)

| (Bitwise OR) (Transact-SQL)

|= (Bitwise OR EQUALS) (Transact-SQL)

^ (Bitwise Exclusive OR) (Transact-SQL)

^= (Bitwise Exclusive OR EQUALS) (Transact-SQL)

~ (Bitwise NOT) (Transact-SQL)

= (Equals) (Transact-SQL)

> (Greater Than) (Transact-SQL)

< (Less Than) (Transact-SQL)

>= (Greater Than or Equal To) (Transact-SQL)

<= (Less Than or Equal To) (Transact-SQL)

<> (Not Equal To) (Transact-SQL)

!< (Not Less Than) (Transact-SQL)

!= (Not Equal To) (Transact-SQL)

!> (Not Greater Than) (Transact-SQL)

-- (Comment) (Transact-SQL)

/*...*/ (Comment) (Transact-SQL)

[ ] (Wildcard - Character(s) to Match) (Transact-SQL)

[^] (Wildcard - Character(s) Not to Match) (Transact-SQL)

_ (Wildcard - Match One Character) (Transact-SQL)

$PARTITION (Transact-SQL)

@@CONNECTIONS (Transact-SQL)

@@CPU_BUSY (Transact-SQL)

@@CURSOR_ROWS (Transact-SQL)

@@DATEFIRST (Transact-SQL)

@@DBTS (Transact-SQL)

@@ERROR (Transact-SQL)

@@FETCH_STATUS (Transact-SQL)

@@IDENTITY (Transact-SQL)

@@IDLE (Transact-SQL)

@@IO_BUSY (Transact-SQL)

@@LANGID (Transact-SQL)

@@LANGUAGE (Transact-SQL)

@@LOCK_TIMEOUT (Transact-SQL)

@@MAX_CONNECTIONS (Transact-SQL)

@@MAX_PRECISION (Transact-SQL)

@@NESTLEVEL (Transact-SQL)

@@OPTIONS (Transact-SQL)

@@PACK_RECEIVED (Transact-SQL)

@@PACK_SENT (Transact-SQL)

@@PACKET_ERRORS (Transact-SQL)

@@PROCID (Transact-SQL)

@@REMSERVER (Transact-SQL)

@@ROWCOUNT (Transact-SQL)

@@SERVERNAME (Transact-SQL)

@@SERVICENAME (Transact-SQL)

@@SPID (Transact-SQL)

@@TEXTSIZE (Transact-SQL)

@@TIMETICKS (Transact-SQL)

@@TOTAL_ERRORS (Transact-SQL)

@@TOTAL_READ (Transact-SQL)

@@TOTAL_WRITE (Transact-SQL)

@@TRANCOUNT (Transact-SQL)

@@VERSION (Transact-SQL)

ABS (Transact-SQL)

ACOS (Transact-SQL)

ADD SIGNATURE (Transact-SQL)

ALL (Transact-SQL)

ALTER Statements (Transact-SQL)

AND (Transact-SQL)

ANY (Transact-SQL)

APPLOCK_MODE (Transact-SQL)

APPLOCK_TEST (Transact-SQL)

APP_NAME (Transact-SQL)

ASCII (Transact-SQL)

ASIN (Transact-SQL)

ASSEMBLYPROPERTY (Transact-SQL)

ASYMKEY_ID (Transact-SQL)

ASYMKEYPROPERTY (Transact-SQL)

ATAN (Transact-SQL)

ATN2 (Transact-SQL)

AVG (Transact-SQL)

BACKUP (Transact-SQL)

BACKUP CERTIFICATE (Transact-SQL)

BACKUP MASTER KEY (Transact-SQL)

BACKUP SERVICE MASTER KEY (Transact-SQL)

BEGIN...END (Transact-SQL)

BEGIN CONVERSATION TIMER (Transact-SQL)

BEGIN DIALOG CONVERSATION (Transact-SQL)

BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)

BEGIN TRANSACTION (Transact-SQL)

BETWEEN (Transact-SQL)

binary and varbinary (Transact-SQL)

BINARY_CHECKSUM (Transact-SQL)

bit (Transact-SQL)

BREAK (Transact-SQL)

BULK INSERT (Transact-SQL)

CASE (Transact-SQL)

CAST and CONVERT (Transact-SQL)

CATCH (TRY...CATCH) (Transact-SQL)

CEILING (Transact-SQL)

CERTPROPERTY (Transact-SQL)

Cert_ID (Transact-SQL)

Change Data Capture Functions (Transact-SQL)

Change Tracking Functions (Transact-SQL)

char and varchar (Transact-SQL)

CHAR (Transact-SQL)

CHARINDEX (Transact-SQL)

CHECKPOINT (Transact-SQL)

CHECKSUM (Transact-SQL)

CHECKSUM_AGG

CLOSE (Transact-SQL)

CLOSE MASTER KEY (Transact-SQL)

CLOSE SYMMETRIC KEY (Transact-SQL)

COALESCE (Transact-SQL)

COLLATE (Transact-SQL)

COLLATIONPROPERTY (Transact-SQL)

COL_LENGTH (Transact-SQL)

COL_NAME (Transact-SQL)

COLUMNPROPERTY (Transact-SQL)

COLUMNS_UPDATED (Transact-SQL)

COMMIT TRANSACTION (Transact-SQL)

COMMIT WORK (Transact-SQL)

Compound Operators (Transact-SQL)

COMPUTE (Transact-SQL)

CONNECTIONPROPERTY (Transact-SQL)

Constants (Transact-SQL)

CONTAINS (Transact-SQL)

CONTAINSTABLE (Transact-SQL)

CONTINUE (Transact-SQL)

CONTEXT_INFO (Transact-SQL)

Control-of-Flow Language (Transact-SQL)

CONVERT (Transact-SQL)

COS (Transact-SQL)

COT (Transact-SQL)

COUNT (Transact-SQL)

COUNT_BIG (Transact-SQL)

CREATE Statements (Transact-SQL)

CRYPT_GEN_RANDOM (Transact-SQL)

CURRENT_REQUEST_ID (Transact-SQL)

CURRENT_TIMESTAMP (Transact-SQL)

CURRENT_USER (Transact-SQL)

cursor (Transact-SQL)

CURSOR_STATUS (Transact-SQL)

Cursors (Transact-SQL)

Data Collector Functions (Transact-SQL)

Data Types (Transact-SQL)

DATABASE_PRINCIPAL_ID (Transact-SQL)

DATABASEPROPERTY (Transact-SQL)

DATABASEPROPERTYEX (Transact-SQL)

DATALENGTH (Transact-SQL)

date (Transact-SQL)

DATEADD (Transact-SQL)

DATEDIFF (Transact-SQL)

DATENAME (Transact-SQL)

DATEPART (Transact-SQL)

datetime (Transact-SQL)

datetime2 (Transact-SQL)

datetimeoffset (Transact-SQL)

DAY (Transact-SQL)

DB_ID (Transact-SQL)

DB_NAME (Transact-SQL)

DBCC (Transact-SQL)

DEALLOCATE (Transact-SQL)

decimal and numeric (Transact-SQL)

DECLARE @local_variable (Transact-SQL)

DECLARE CURSOR (Transact-SQL)

DECRYPTBYASYMKEY (Transact-SQL)

DECRYPTBYCERT (Transact-SQL)

DECRYPTBYKEY (Transact-SQL)

DECRYPTBYKEYAUTOASYMKEY (Transact-SQL)

DECRYPTBYKEYAUTOCERT (Transact-SQL)

DECRYPTBYPASSPHRASE (Transact-SQL)

DEGREES (Transact-SQL)

DELETE (Transact-SQL)

DENSE_RANK (Transact-SQL)

DENY (Transact-SQL)

DIFFERENCE (Transact-SQL)

DISABLE TRIGGER (Transact-SQL)

DROP Statements (Transact-SQL)

ELSE (IF...ELSE) (Transact-SQL)

ENABLE TRIGGER (Transact-SQL)

ENCRYPTBYASYMKEY (Transact-SQL)

ENCRYPTBYCERT (Transact-SQL)

ENCRYPTBYKEY (Transact-SQL)

ENCRYPTBYPASSPHRASE (Transact-SQL)

END (BEGIN...END) (Transact-SQL)

END CONVERSATION (Transact-SQL)

ERROR_LINE (Transact-SQL)

ERROR_MESSAGE (Transact-SQL)

ERROR_NUMBER (Transact-SQL)

ERROR_PROCEDURE (Transact-SQL)

ERROR_SEVERITY (Transact-SQL)

ERROR_STATE (Transact-SQL)

EVENTDATA (Transact-SQL)

EXCEPT and INTERSECT (Transact-SQL)

EXECUTE AS (Transact-SQL)

EXECUTE AS Clause (Transact-SQL)

EXECUTE

EXISTS (Transact-SQL)

EXP (Transact-SQL)

Expressions (Transact-SQL)

FETCH (Transact-SQL)

FILE_ID (Transact-SQL)

FILE_IDEX (Transact-SQL)

FILE_NAME (Transact-SQL)

FILEGROUP_ID (Transact-SQL)

FILEGROUP_NAME (Transact-SQL)

FILEGROUPPROPERTY (Transact-SQL)

FILEPROPERTY (Transact-SQL)

float and real (Transact-SQL)

FLOOR (Transact-SQL)

sys.fn_check_object_signatures (Transact-SQL)

fn_get_audit_file (Transact-SQL)

fn_get_sql (Transact-SQL)

fn_helpcollations (Transact-SQL)

fn_listextendedproperty (Transact-SQL)

sys.fn_xe_file_target_read_file (Transact-SQL)

fn_my_permissions (Transact-SQL)

fn_servershareddrives (Transact-SQL)

fn_trace_geteventinfo (Transact-SQL)

fn_trace_getfilterinfo (Transact-SQL)

fn_trace_getinfo (Transact-SQL)

fn_trace_gettable (Transact-SQL)

fn_virtualfilestats (Transact-SQL)

fn_virtualservernodes (Transact-SQL)

FOR Clause (Transact-SQL)

FORMATMESSAGE (Transact-SQL)

FREETEXT (Transact-SQL)

FREETEXTTABLE (Transact-SQL)

FROM (Transact-SQL)

FULLTEXTCATALOGPROPERTY (Transact-SQL)

FULLTEXTSERVICEPROPERTY (Transact-SQL)

Functions (Transact-SQL)

geography (Transact-SQL)

geometry (Transact-SQL)

GET CONVERSATION GROUP (Transact-SQL)

GET_FILESTREAM_TRANSACTION_CONTEXT (Transact-SQL)

GET_TRANSMISSION_STATUS (Transact-SQL)

GETANSINULL (Transact-SQL)

GETDATE (Transact-SQL)

GETUTCDATE (Transact-SQL)

GO (Transact-SQL)

GOTO (Transact-SQL)

GRANT (Transact-SQL)

GROUP BY (Transact-SQL)

GROUPING (Transact-SQL)

GROUPING_ID (Transact-SQL)

HAS_DBACCESS (Transact-SQL)

HAS_PERMS_BY_NAME (Transact-SQL)

HASHBYTES (Transact-SQL)

HAVING (Transact-SQL)

hierarchyid (Transact-SQL)

Hints (Transact-SQL)

HOST_ID (Transact-SQL)

HOST_NAME (Transact-SQL)

IDENT_CURRENT (Transact-SQL)

IDENT_INCR (Transact-SQL)

IDENT_SEED (Transact-SQL)

IDENTITY (Function) (Transact-SQL)

IDENTITY (Property) (Transact-SQL)

IF...ELSE (Transact-SQL)

image (Transact-SQL)

IN (Transact-SQL)

INDEXKEY_PROPERTY (Transact-SQL)

INDEXPROPERTY (Transact-SQL)

INDEX_COL (Transact-SQL)

INSERT (Transact-SQL)

int, bigint, smallint, and tinyint (Transact-SQL)

INTO Clause (Transact-SQL)

IS_MEMBER (Transact-SQL)

IS_OBJECTSIGNED (Transact-SQL)

IS_SRVROLEMEMBER (Transact-SQL)

ISDATE (Transact-SQL)

IS [NOT] NULL (Transact-SQL)

ISNULL (Transact-SQL)

ISNUMERIC (Transact-SQL)

Key_GUID (Transact-SQL)

Key_ID (Transact-SQL)

KEY_NAME (Transact-SQL)

KILL (Transact-SQL)

KILL QUERY NOTIFICATION SUBSCRIPTION (Transact-SQL)

KILL STATS JOB (Transact-SQL)

LEFT (Transact-SQL)

LEN (Transact-SQL)

LIKE (Transact-SQL)

LOG (Transact-SQL)

LOG10 (Transact-SQL)

LOGINPROPERTY (Transact-SQL)

LOWER (Transact-SQL)

LTRIM (Transact-SQL)

MAX (Transact-SQL)

MERGE (Transact-SQL)

MIN (Transact-SQL)

MIN_ACTIVE_ROWVERSION (Transact-SQL)

money and smallmoney (Transact-SQL)

MONTH (Transact-SQL)

MOVE CONVERSATION (Transact-SQL)

NCHAR (Transact-SQL)

nchar and nvarchar (Transact-SQL)

NEWID (Transact-SQL)

NEWSEQUENTIALID() (Transact-SQL)

NOT (Transact-SQL)

ntext, text, and image (Transact-SQL)

NTILE (Transact-SQL)

NULLIF (Transact-SQL)

numeric (Transact-SQL)

OBJECT_DEFINITION (Transact-SQL)

OBJECT_ID (Transact-SQL)

OBJECT_NAME (Transact-SQL)

OBJECT_SCHEMA_NAME (Transact-SQL)

OBJECTPROPERTY (Transact-SQL)

OBJECTPROPERTYEX (Transact-SQL)

ODBC Scalar Functions (Transact-SQL)

OPEN (Transact-SQL)

OPEN MASTER KEY (Transact-SQL)

OPEN SYMMETRIC KEY (Transact-SQL)

OPENDATASOURCE (Transact-SQL)

OPENQUERY (Transact-SQL)

OPENROWSET (Transact-SQL)

OPENXML (Transact-SQL)

Operators (Transact-SQL)

OPTION Clause (Transact-SQL)

OR (Transact-SQL)

ORDER BY Clause (Transact-SQL)

ORIGINAL_DB_NAME (Transact-SQL)

ORIGINAL_LOGIN (Transact-SQL)

OUTPUT Clause (Transact-SQL)

OVER Clause (Transact-SQL)

PARSENAME (Transact-SQL)

PathName (Transact-SQL)

PATINDEX (Transact-SQL)

PERMISSIONS (Transact-SQL)

PI (Transact-SQL)

POWER (Transact-SQL)

Predicate (Transact-SQL)

PRINT (Transact-SQL)

PUBLISHINGSERVERNAME (Transact-SQL)

PWDCOMPARE (Transact-SQL)

PWDENCRYPT (Transact-SQL)

QUOTENAME (Transact-SQL)

RADIANS (Transact-SQL)

RAISERROR

RAND (Transact-SQL)

RANK (Transact-SQL)

READTEXT (Transact-SQL)

real (Transact-SQL)

RECEIVE (Transact-SQL)

RECONFIGURE (Transact-SQL)

REPLACE (Transact-SQL)

REPLICATE (Transact-SQL)

Reserved Keywords (Transact-SQL)

RESTORE Statements for Restoring, Recovering, and Managing Backups (Transact-SQL)

RESTORE MASTER KEY (Transact-SQL)

RESTORE SERVICE MASTER KEY (Transact-SQL)

RETURN (Transact-SQL)

REVERSE (Transact-SQL)

REVERT (Transact-SQL)

REVOKE (Transact-SQL)

RIGHT (Transact-SQL)

ROLLBACK TRANSACTION (Transact-SQL)

ROLLBACK WORK (Transact-SQL)

ROUND (Transact-SQL)

ROW_NUMBER (Transact-SQL)

ROWCOUNT_BIG (Transact-SQL)

rowversion (Transact-SQL)

RTRIM (Transact-SQL)

SAVE TRANSACTION (Transact-SQL)

SCHEMA_ID (Transact-SQL)

SCHEMA_NAME (Transact-SQL)

SCOPE_IDENTITY (Transact-SQL)

Search Condition (Transact-SQL)

SELECT @local_variable (Transact-SQL)

SELECT (Transact-SQL)

SEND (Transact-SQL)

SERVERPROPERTY (Transact-SQL)

SESSION_USER (Transact-SQL)

SESSIONPROPERTY (Transact-SQL)

SET @local_variable (Transact-SQL)

SET (Transact-SQL)

SETUSER (Transact-SQL)

SHUTDOWN (Transact-SQL)

SIGN (Transact-SQL)

SignByAsymKey (Transact-SQL)

SignByCert (Transact-SQL)

SIN (Transact-SQL)

smalldatetime (Transact-SQL)

smallint (Transact-SQL)

smallmoney (Transact-SQL)

SOME | ANY (Transact-SQL)

SOUNDEX (Transact-SQL)

SPACE (Transact-SQL)

sql_variant (Transact-SQL)

SQL_VARIANT_PROPERTY (Transact-SQL)

SQRT (Transact-SQL)

SQUARE (Transact-SQL)

STATS_DATE (Transact-SQL)

STDEV (Transact-SQL)

STDEVP (Transact-SQL)

STR (Transact-SQL)

STUFF (Transact-SQL)

SUBSTRING (Transact-SQL)

SUM (Transact-SQL)

SUSER_ID (Transact-SQL)

SUSER_NAME (Transact-SQL)

SUSER_SID (Transact-SQL)

SUSER_SNAME (Transact-SQL)

SWITCHOFFSET (Transact-SQL)

SYSDATETIME (Transact-SQL)

SYSDATETIMEOFFSET (Transact-SQL)

SYMKEYPROPERTY (Transact-SQL)

System Stored Procedures (Transact-SQL)

System Tables (Transact-SQL)

System Views (Transact-SQL)

SYSUTCDATETIME (Transact-SQL)

sys.fn_builtin_permissions (Transact-SQL)

sys.fn_translate_permissions (Transact-SQL)

sys.fn_validate_plan_guide (Transact-SQL)

sys.login_token (Transact-SQL)

sys.user_token (Transact-SQL)

SYSTEM_USER (Transact-SQL)

table (Transact-SQL)

Table Value Constructor (Transact-SQL)

TAN (Transact-SQL)

TERTIARY_WEIGHTS (Transact-SQL)

text (Transact-SQL)

TEXTPTR (Transact-SQL)

TEXTVALID (Transact-SQL)

time (Transact-SQL)

tinyint (Transact-SQL)

TODATETIMEOFFSET (Transact-SQL)

TOP (Transact-SQL)

Trace Flags (Transact-SQL)

Transactions (Transact-SQL)

TRY...CATCH (Transact-SQL)

TRIGGER_NESTLEVEL (Transact-SQL)

TRUNCATE TABLE (Transact-SQL)

TYPE_ID (Transact-SQL)

TYPE_NAME (Transact-SQL)

TYPEPROPERTY (Transact-SQL)

UNICODE (Transact-SQL)

UNION (Transact-SQL)

uniqueidentifier (Transact-SQL)

UPDATE (Transact-SQL)

UPDATE() (Transact-SQL)

UPDATE STATISTICS (Transact-SQL)

UPDATETEXT (Transact-SQL)

UPPER (Transact-SQL)

USE (Transact-SQL)

USER (Transact-SQL)

USER_ID (Transact-SQL)

USER_NAME (Transact-SQL)

VAR (Transact-SQL)

varbinary (Transact-SQL)

varchar (Transact-SQL)

VARP (Transact-SQL)

VerifySignedByCert (Transact-SQL)

VerifySignedByAsymKey (Transact-SQL)

WAITFOR (Transact-SQL)

WHERE (Transact-SQL)

WHILE (Transact-SQL)

WITH common_table_expression (Transact-SQL)

WITH XMLNAMESPACES (Transact-SQL)

WRITETEXT (Transact-SQL)

XACT_STATE (Transact-SQL)

xml (Transact-SQL)

xml_schema_namespace (Transact-SQL)

YEAR (Transact-SQL)

Community Content

Add code samples and tips to enhance this topic.

More...

0 out of 2 rated this helpful - Rate this topic

Control-of-Flow Language (Transact-SQL)

SQL Server 2008

Other Versions

The Transact-SQL control-of-flow language keywords are:

BEGIN...END	RETURN
BREAK	TRY...CATCH
CONTINUE	WAITFOR
GOTO label	WHILE
IF...ELSE

Reference

CASE (Transact-SQL)

/*...*/ (Comment) (Transact-SQL)

-- (Comment) (Transact-SQL)

DECLARE @local_variable (Transact-SQL)

EXECUTE (Transact-SQL)

PRINT (Transact-SQL)

RAISERROR (Transact-SQL)

Concepts

Batches

Control-of-Flow

Elements of Transact-SQL

Other Resources

Handling Database Engine Errors

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Content Add

FAQ

Site Feedback

Tell us about your experience...

Did the page load quickly?

Yes No

Do you like the page design?

Yes No

Tell us more