Stored Procedure With Common Table Expression Or CTE

In this post, we will see how to use common table expression or CTE in our SQL Server. There are so many situations in which you may need to use a common table expression. I had a situation of returning ROW_NUMBER variable value for my paging query in SQL, for this, I used CTE. A common table expression is actually a temporary result set or a table whose scope is defined or limited to the current statement. In this post, I will explain the same in detail. I hope you will like this.

Background

I had a situation of using a paging query for one of my applications which actually load data to a grid on demand, like when user scrolls or does an y paging. For this, I needed to create a stored procedure which accepts page offset as a parameter and return the data accordingly. I used Common Table Expression for the same.

When to Use a CTE

There are some situations that you may need to use a CTE, few of them are listed below:

• When you are working with recursive queries
• When you need to reference a temporary variable in your query
• You can create temporary views by using CTE, so that you do not need to store the details as view

Using the Code

I hope you all got an idea about CTE, now we can see the basic structure of a common table expression.

WITH CTE_Name(Column_Names,...) AS
  (
	--Select Query
  )
SELECT *
FROM CTE_Name
WHERE Column_Names1>=Your Condition
END

With the above structure, I have created my own stored procedure as follows:

USE [TrialsDB]
GO
/****** Object:  StoredProcedure 
[dbo].[usp_Get_SalesOrderDetailPage]    Script Date: 25-Feb-16 12:53:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,Sibeesh Venu>
-- Create date: <Create Date, 18-Feb-2016>
-- Description:	<Description,To fetch SalesOrderDetail Page Wise>
-- =============================================
ALTER PROCEDURE [dbo].[usp_Get_SalesOrderDetailPage] _
@pageOffset int=0 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.

SET NOCOUNT ON;

WITH CTE_Sales(SlNo, SalesOrderID,SalesOrderDetailID,_
CarrierTrackingNumber,OrderQty,ProductID,UnitPrice,ModifiedDate) AS
  ( SELECT ROW_NUMBER() over (
                              ORDER BY ModifiedDate DESC) AS SlNo,
                        SalesOrderID,
                        SalesOrderDetailID,
                        CarrierTrackingNumber,
                        OrderQty,
                        ProductID,
                        UnitPrice,
                        ModifiedDate
   FROM dbo.SalesOrderDetail)
SELECT *
FROM CTE_Sales
WHERE SlNo>=@pageOffset
  AND SlNo<@pageOffset+10 END

--[usp_Get_SalesOrderDetailPage] 4

As you can see, in the select query, I am using a temporary column SlNo which is actually a result of ROW_NUMBER(). So to use this query in a where condition, I was forced to use the CTE. Now let us run our stored procedure and see the output.

Output

Conclusion

Did I miss anything you may think is needed? Did you try CTE in your query? Have you ever wanted to do this? Could you find this post useful? I hope you liked this article. Please share your valuable suggestions and feedback.

Your Turn. What Do You Think?

A blog isn’t a blog without comments, but do try to stay on topic. If you have a question unrelated to this post, you’re better off posting it on C# Corner, Code Project, Stack Overflow, ASP.NET Forum instead of commenting here. Tweet or email me a link to your question there and I’ll definitely try to help if I can.