Post
Ask Question

Stored Procedure With Common Table Expression Or CTE

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.

  1. WITH CTE_Name(Column_Names,...) AS  
  2. (  
  3. --Select Query  
  4. )  
  5. SELECT *  
  6. FROM CTE_Name  
  7. WHERE Column_Names1>=Your Condition  
  8. END  

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

  1. USE [TrialsDB]  
  2. GO  
  3. /****** Object: StoredProcedure [dbo].[usp_Get_SalesOrderDetailPage] Script Date: 25-Feb-16 12:53:07 PM ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. -- =============================================  
  9. -- Author: <Author,Sibeesh Venu>  
  10. -- Create date: <Create Date, 18-Feb-2016>  
  11. -- Description: <Description,To fetch SalesOrderDetail Page Wise>  
  12. -- =============================================  
  13. ALTER PROCEDURE [dbo].[usp_Get_SalesOrderDetailPage] @pageOffset int=0 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from  
  14. -- interfering with SELECT statements.  
  15. SET NOCOUNT ON;  
  16. WITH CTE_Sales(SlNo, SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,OrderQty,ProductID,UnitPrice,ModifiedDate) AS  
  17. ( SELECT ROW_NUMBER() over (  
  18. ORDER BY ModifiedDate DESC) AS SlNo,  
  19. SalesOrderID,  
  20. SalesOrderDetailID,  
  21. CarrierTrackingNumber,  
  22. OrderQty,  
  23. ProductID,  
  24. UnitPrice,  
  25. ModifiedDate  
  26. FROM dbo.SalesOrderDetail)  
  27. SELECT *  
  28. FROM CTE_Sales  
  29. WHERE SlNo>=@pageOffset  
  30. AND SlNo<@pageOffset+10 END  
  31. --[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
                              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.

Read more articles on SQL Server:


Similar Articles