Stored Procedure With Common Table Expression Or CTE

Sibeesh Venu

18 Comments

Join the conversation! Your thoughts help the community grow.

Sibeesh Venu10y ago
Amit Singh Thanks much
Amit Kumar Singh10y ago
Good one
Sibeesh Venu10y ago
Shubham Kumar Thanks a lot
Shubham Kumar10y ago
nice
Sibeesh Venu10y ago
Raja T Thanks much
Sibeesh Venu10y ago
Gowtham K Thanks much
Sibeesh Venu10y ago
Ehsan Sajjad Thanks much
Sibeesh Venu10y ago
Vignesh Mani Thanks much
Raja T10y ago
Nice, Thanks for sharing
Gowtham K10y ago
Good One ,Thanks for sharing
Ehsan Sajjad10y ago
Thanks for this
Vignesh Mani10y ago
Good
Sibeesh Venu10y ago
Pankaj Kumar Choudhary Thanks a lot, Yes you are right!.
Sibeesh Venu10y ago
Md Almajid Koushik Thanks a lot
Sibeesh Venu10y ago
Asfend Yar Thanks a lot
Pankaj Kumar Choudhary10y ago
Nice Use Of CTE. A CTE can make a reference to itself. An example of self-referencing is Recursion. If you want to recursively enumerate organization hierarchy data, recursive CTE is easy to implement and increases readability. CTE is easy to implement when compared to complex queries which involves several sub-queries.
Md Almajid Koushik10y ago
Nice!!!!!
Asfend Yar10y ago
good job done

There are so many situations that 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 Server; 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.

Please see this article in my blog here.

Background

I had a situation of using a paging query for one of my applications which actually loads data to a grid on demand, like when a user scrolls or does any paging. For this I needed to create a stored procedure which accepts page offset as a parameter and returns 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, a 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

Stored Procedure With Common Table Expression Or CTE

Conclusion

Did I miss anything that you may think is needed? Did you try CTE in your query? Have you ever wanted to do this requirement? Did you find this post useful? I hope you liked this article. Please share with me 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.