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.
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
WHERE Column_Names1>=Your Condition
With the above structure, I have created my own stored procedure as follows:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[usp_Get_SalesOrderDetailPage] _
@pageOffset int=0 AS BEGIN
SET NOCOUNT ON;
WITH CTE_Sales(SlNo, SalesOrderID,SalesOrderDetailID,_
( SELECT ROW_NUMBER() over (
ORDER BY ModifiedDate DESC) AS SlNo,
AND SlNo<@pageOffset+10 END
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.
Stored Procedure With Common Table Expression Or CTE
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.