Resources » .NET programming » Databases

Use of With Clause in Sql Server


Last Updated:   Category: Databases    
Author: Member Level: Gold    Points: 20



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



Did you like this resource? Share it with your friends and show your love!

Responses to "Use of With Clause in Sql Server"
Author: Mahesh Nagar    23 May 2011Member 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



SQL-Server-CTE-Basics.htm
Author: priya narayan    28 May 2011Member Level: Gold   Points : 0
Thanks for sharing the Article.


Feedbacks      

Post Comment:




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


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    solaris
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India