Use of With Clause in Sql Server Articles, samples and tutorials

With Clause comes handy when you want to work with a subset of data in your queries.
In SQL, Common Table Expression is created using the WITH statement followed by the CTE name. A Common Table Expression is an expression that returns a temporary result set from inside a statement. This result set is similar Temporary Table. If a Temporary Table is used, first we have to be create, and populate it and it could be called over and over again from within a statement but a Common Table Expression must be called immediately after stating it.
Syntax:


WITH expression_name [ ( column_name [,...n] ) ] 
AS 
   ( CTE_query_definition )

The statement to run the CTE is:


SELECT  FROM expression_name;

Example:


WITH TenantDetails(TenantID,TenantName,TenantNickName) AS
(
   SELECT TenantID,TenantName,TenantNickName
   FROM tblTenant
   WHERE clientid = @ClientID
)
SELECT * 
FROM TenantDetails

In the below given example, the call to the CTE TenantDetails will fail.


WITH TenantDetails(TenantID,TenantName,TenantNickName) AS
(
   SELECT TenantID,TenantName,TenantNickName
   FROM tblTenant
   WHERE clientid = @ClientID

)

SELECT * FROM tblperson

SELECT * 
FROM TenantDetails

The call to TenantDetails will fail with the following error:

Msg 208, Level 16, State 1, Line 8
Invalid object name 'TenantDetails'.

So instead of using the subquery repeatively we can make use of CTE TenantDetails as below.



SELECT  @ClientID
		     , Tenant.TenantID
		     , Tenant.TenantName
		     , Tenant.TenantNickname
		     , Tenant.TenantNickname 
		 FROM  tblorg Org JOIN TenantDetails Tenant
		    ON Tenant.TenantID	= Org.TenantID
		  JOIN tblAddress ta
		    ON ta.SourceID	= Org.OrgID
		   AND ta.AddressTypeID	= 19
		   AND ta.Phone= ''

		 UNION ALL

		
		SELECT @ClientID
		     , Tenant.TenantID
		     , Tenant.TenantName
		     , Tenant.TenantNickname
		     , Tenant.TenantNickname 		       
		  FROM tblorg Org JOIN TenantDetails Tenant
		    ON Tenant.TenantID		= Org.TenantID
		   AND Org.OrgName		= ''


		 UNION ALL

	
		SELECT @ClientID
		     , Tenant.TenantID
		     , Tenant.TenantName
		     , Tenant.TenantNickname
		     , Tenant.TenantNickname 		
		  FROM tblorg Org JOIN TenantDetails Tenant
		    ON Tenant.TenantID		= Org.TenantID
		   AND Org.OrgNickName		= ''

Post Comment:

Notify me by email when others post comments to this article.

Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.

No HTML formatting and links to other web sites are allowed.

This is a strictly moderated site. Absolutely no spam allowed.

Name:	Sign In to fill automatically.
Email:	(Will not be published, but required to validate comment)
	Type the numbers and letters shown on the left.