Stored Procedure With Common Table Expression Or CTE

Sibeesh Venu
8y
5.9k
0
3

Article

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.