With statement and it's usage in sql server 2008

A new feature introduced in sql server 2005 named common table expression, it's really cool feature to use in many cases to solve the complicated problems in sql server.

As defined by the Microsoft: It specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. A common table expression can include references to itself. This is referred to as a recursive common table expression.

Let's see some examples:

Paginated data: suppose you want to get only paged data (only 50 records if page size is 50) in a typical search on page

Create Proc [Product_Search]
       @searchTitle    VARCHAR(100)
      ,@pageindex     INT 
      ,@numrows        INT 
 AS

 Declare @startRowIndex INT
 Set @startRowIndex = (@pageindex * @numrows)
 ;with TempTable AS
 (
   Select ROW_NUMBER()OVER (Order By P.CreationDate  DESC)AS [RowID]   
       , P.ProductID
   From Products P
   Where  Title Like '%' + @searchTitle +' %'  AND Category = 1
 )
 Select P.*
 From Products P
      Inner Join TempTable T ON P.ProductID = T.ProductID
 Where T.RowID Between   @startRowIndex + 1 AND @startRowIndex +@numrows 
 Order By T.RowID

Things to take care: I used ;with TempTable AS

  1. ; (semicolon) is needed here because with should be the very first line which is not here so I used ;
  2. I have not used the column name because internally I have given the column name for every column which i am selecting, if you want to define the column name on the top then your with would be like this

    With TempTable (RowID, ProductID) AS
    

Recursive data: shows managers and the employees reporting to them, up to 5 level

 With ReportTo(ManagerID, EmployeeID, Title, EmployeeLevel) AS 
 (
      Select ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
      From MyEmployees 
      Where ManagerID IS NULL
      Union All
      Select e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
      From MyEmployees AS e
           Inner Join ReportTo AS R ON e.ManagerID = R.EmployeeID 
 )
 Select ManagerID, EmployeeID, Title, EmployeeLevel 
 From ReportTo
 Where EmployeeLevel <= 5

MAXRECURSION to cancel a statement to enter into infinite loop

 With ReportTo(ManagerID, EmployeeID, Title) AS 
 (
      Select ManagerID, EmployeeID, Title
      From MyEmployees 
      Where ManagerID IS NULL
      Union All
      Select e.ManagerID, e.EmployeeID, e.Title
      From ReportTo AS R 
           Join MyEmployees AS e  ON e.ManagerID = R.EmployeeID 
 )
 Select ManagerID, EmployeeID, Title, EmployeeLevel 
 From ReportTo
 OPTION (MAXRECURSION 5)

try to get reason of infinite loop, in above code change

From ReportTo AS R 
    Join MyEmployees AS e  ON e.ManagerID = R.EmployeeID 
TO
From MyEmployees AS e  
    Join ReportTo AS R  ON e.ManagerID = R.EmployeeID

-- and remove the last line "OPTION (MAXRECURSION 5)" 
-- becuase it is no more needed your code will never go to infinite loop
Liam Kelley
  • sql server
By Liam Kelley On 04 Jul, 12  Viewed: 336

Other blogs you may like

Adding identity to an existing column in SQL Server

We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi   On 02 May 2013  Viewed: 783

some useful sql tricks

There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi   On 31 Mar 2013  Viewed: 844

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty   On 26 Mar 2013  Viewed: 2,408

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D   On 25 Feb 2013  Viewed: 582

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi   On 13 Feb 2013  Viewed: 11,100