Use of With Clause in Sql Server

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


Comments

Author: Mahesh Nagar23 May 2011 Member Level: Gold   Points : 1

Thanks dear

thanks for valuable information and after reading that i search this topic on internet and fount more details in your article you just mention non recursive and i add now SQL Server CTE Basics

use this link to fine tune details

www.simple-talk.com
content/article.aspx
article=1019

OR
use this attachment

Author: priya narayan28 May 2011 Member Level: Gold   Points : 0

Thanks for sharing the 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:
    Email: