Sibeesh Passion

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 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, 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 application which actually load data to a grid on demand, like when user scrolls or do 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

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

    USE [TrialsDB]
    /****** Object:  StoredProcedure [dbo].[usp_Get_SalesOrderDetailPage]    Script Date: 25-Feb-16 12:53:07 PM ******/
    -- =============================================
    -- 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.
    WITH CTE_Sales(SlNo, SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,OrderQty,ProductID,UnitPrice,ModifiedDate) AS
      ( SELECT ROW_NUMBER() over (
                                  ORDER BY ModifiedDate DESC) AS SlNo,
       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 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

    Stored Procedure With Common Table Expression Or CTE


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

    Kindest Regards
    Sibeesh Venu

    Footer With Address And Phones