Page History: T-SQL Quick Reference - SQL Server

Compare Page Revisions

Page Revision: Thu, Jun 11, 2009, 3:19 PM

.<1> - }

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
  |-
  | align="center" | @
  | Local variable or parameter
  |-
  | align="center" | @@
  | Global variable
  |-
  | align="center" | #
  | Temporary table or procedure
  |-
  | align="center" | ##
  | Global temporary object
  |}
  
  >

  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¶

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

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