WITH Common Table Expressions – SQL Server Syntax Example: WITH AS – T-SQL Example

Get 5 Hours of FREE PREMIUM Videos:

LearnVisualStudio.NET Free Preview

LearnVisualStudio Premium Videos

dotNetVideos Premium Video Catalog

WITH Common Table Expressions – SQL Server Syntax Example: WITH AS – T-SQL Example

Purpose: – Illustrates the SQL Server syntax for With Common Table Expressions.

SYNTAX:

[ WITH common_table_expression [ (column_name [ ,...n ] ) ]
AS
( SELECT [ ALL | DISTINCT ]
[TOP ( expression ) [PERCENT] [ WITH TIES ] ]
column_list
[ FROM { table_source } [ ,...n ] ]
[ WHERE select_condition]
[ GROUP BY ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ] )

PURPOSE:
WITH allows you to create a temporary result set that is called a common table expression (CTE). The CTE has column names that match up with the column names listed in the SELECT statement that follows the AS keyword and that is enclosed in parentheses. When a CTE contains a reference to itself, this is called a recursive CTE. For a more complete description of syntax and examples, please refer to: http://msdn.microsoft.com/en-us/library/ms175972(v=SQL.105).aspx

Code Sample for WITH Common Table Expressions:

 T-SQL | 
 copy code |
?
 
 
/* WITH example from http://idealprogrammer.com 
 
 PURPOSE: WITH allows you to create a temporary result set that is called a common table expression
 (CTE). The CTE has column names that match up with the column names listed in the SELECT statement
 that follows the AS keyword and that is enclosed in parentheses. When a CTE contains a reference
 to itself, this is called a recursive CTE. For a more complete description of syntax and examples,
 please refer to: http://msdn.microsoft.com/en-us/library/ms175972(v=SQL.105).aspx
          
 
 SYNTAX: 
 
 [ WITH common_table_expression [ (column_name [ ,...n ] ) ]
    AS
    ( SELECT [ ALL | DISTINCT ] 
			[TOP ( expression ) [PERCENT] [ WITH TIES ] ] 
			 column_list  		
			[ FROM { table_source } [ ,...n ] ] 
			[ WHERE select_condition] 
			[ GROUP BY ] 
			[ HAVING  search_condition  ] 
			[ ORDER BY  order_expression [ ASC | DESC ] ] )
 
   
*/
 
 
CREATE TABLE people(
     ID          INT,
     firstname   VARCHAR (20),
     lastname    VARCHAR (20),
     statecode   VARCHAR (2),
     alive       BIT,
     height      INT )
 GO
 
 
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height) 
 VALUES (1,  'Paul', 'Revere', 'AL', 0, 72)
 GO
 
 INSERT INTO people (ID, firstname, lastname, alive, height) -- statecode NULL
 VALUES (2,  'John', 'Lennon',  0, 69)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (3,  'Steve', 'Martin', 'NY', 1, 75)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (4,  'George', 'Washington', 'VA', 0, 75)
 GO
 
 
 
 --Display entire table
 SELECT 'Entire Table' AS Example,* FROM people
 
 
 
 
 -- 1. Example of CTE with simple SELECT 
 GO
 WITH people_CTE(CTE_Example, CTE_firstname, CTE_lastname, CTE_height)
 AS 
 (SELECT 'Example #1' AS Example, firstname, lastname, height 
  FROM people p
  WHERE height > 72)
 
  SELECT * 
  FROM people_CTE
 
 
 
 GO
 
 
 -- 2. For more examples, please see: 
 -- http://msdn.microsoft.com/en-us/library/ms175972(v=SQL.105).aspx
 
 
 
 DROP TABLE people;
 
 GO
 
 
 

Sample Output for With Common Table Expressions Example

Related posts:

1. DISTINCT – SQL Server Syntax Example: DISTINCT – T-SQL Example DISTINCT - SQL Server Syntax Example: DISTINCT - T-SQL Example...
2. ORDER BY – SQL Server Syntax Example: ORDER BY – T-SQL Example ORDER BY - SQL Server Syntax Example: ORDER BY -...
3. NULL – SQL Server Syntax Example: NULL – T-SQL Example NULL - SQL Server Syntax Example: NULL - T-SQL Example...
4. SELECT WHERE Clause- SQL Server Syntax Example: WHERE – T-SQL Example SELECT WHERE Clause- SQL Server Syntax Example: WHERE - T-SQL...
5. NOT – SQL Server Syntax Example: NOT – T-SQL Example NOT - SQL Server Syntax Example: NOT - T-SQL Example...

Related posts brought to you by Yet Another Related Posts Plugin.