Steps to create stored procedure that can be used for custom pagination, sorting and filtering

In this post, let's see how to use the tool that talked about in my earlier post - Dynamic SQL for custom pagination ... to generate the sql stored procedure script.

1. Define columns in the result as follows. Check the boxes Allow Filtering and Allow Sorting if only the columns need to participate in filtering and sorting logic respectively

   

2. Click Generate Code menu to generate the stored procedure. The menu item launches a File Save dialog for you to save the generated script to a file folder.
3. Here is how the generated SQL script looks like:
   

   
   /*******************
   * Operator Enums
   *  Conditions Enum :
   * -1 - No filter
   *  0 - Null
   *  1 - Not Null
   *  2 - Equals
   *  3 - NotEquals
   *  ---- for strings ---
   *  4 - StartsWith
   *  5 - EndsWith
   *  6 - Contains
   *  ---- for int, decimal, datetime ---
   *  7 - GreaterThan
   *  8 - GreaterThanOrEquals
   *  9 - LessThan
   *  10 - LessThanOrEquals
   *******************/
   IF EXISTS (SELECT * FROM dbo.sysobjects
    WHERE id = object_id(N"[dbo].[<procedure_name>]")            AND type in (N"P", N"PC"))
    BEGIN
   DROP PROCEDURE [dbo].[<procedure_name>]
    END
    GO 
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].[<procedure_name>](
   -- TODO: Add more parameters if needed
      @whereClauseXML NVARCHAR(4000) = NULL
    , @startInd Int = 1
    , @endInd BIGINT = 5000
    , @pageSize int = 10 
    , @sortColumn nvarchar(100)  = ""
    , @sortOrder nvarchar(100) = "ASC"
    
    )
    AS
    BEGIN
    
          SET NOCOUNT ON
   
   DECLARE @idoc int
   IF @whereClauseXML IS NULL
   BEGIN
          SET @whereClauseXML = "<ROW><Filter
   FirstNameOperator="-1"
   LastNameOperator="-1"
   DateOfBirthOperator="-1"
    ></Filter></ROW>"
   END
   EXEC sp_xml_preparedocument @idoc OUTPUT, @whereClauseXML
   
   CREATE TABLE #tmpForWhereClause
   (
   FirstNameOperator INT NOT NULL,
   FirstNameValue nvarchar(50) NULL,
   LastNameOperator INT NOT NULL,
   LastNameValue nvarchar(50) NULL,
   DateOfBirthOperator INT NOT NULL,
   DateOfBirthValue datetime NULL
   )
   CREATE TABLE #FinalResults(
          [RowNum] [bigint] NOT NULL IDENTITY(1,1),
          Id uniqueidentifier  NULL ,
          FirstName nvarchar(50)  NULL ,
          LastName nvarchar(50)  NULL ,
          DateOfBirth datetime  NULL
   )
   
   INSERT INTO #tmpForWhereClause
   SELECT 
          ISNULL(FirstNameOperator,-1),
          FirstNameValue ,
          ISNULL(LastNameOperator,-1),
          LastNameValue ,
          ISNULL(DateOfBirthOperator,-1),
          DateOfBirthValue
   FROM   OPENXML (@idoc,"/ROW/Filter",1) WITH (
   FirstNameOperator INT,
   FirstNameValue nvarchar(50)
   ,LastNameOperator INT,
   LastNameValue nvarchar(50)
   ,DateOfBirthOperator INT,
   DateOfBirthValue datetime
    )
   
   --------------------- BEGIN -----------------------------
   ----   Create a temp variable or a CTE with the actual SQL search query ----------
   ----   and use that CTE in the place of <table> in the following SQL statements ---
   ----          or  ---
   ----   If you want to wrap this stored proc around an existing stored procedure then,
   --    Create a #temp table ---
   --    Execute the following SQL –
   --    INSERT INTO #tmpResults
   --    EXEC <your_existing_sp> @param1, @param2 ….
   --------------------- END -----------------------------
   INSERT INTO #FinalResults
   SELECT
          T.Id,
          T.FirstName,
          T.LastName,
          T.DateOfBirth
   FROM <table> T,
   #tmpForWhereClause TMP
   WHERE (
   
   (
           ( TMP.FirstNameOperator = -1 )
    OR
           ( TMP.FirstNameOperator = 0 AND T.FirstName IS NULL )
    OR
           ( TMP.FirstNameOperator = 1 AND T.FirstName IS NOT NULL )
    OR
           ( TMP.FirstNameOperator = 2 AND T.FirstName = TMP.FirstNameValue )
    OR
           ( TMP.FirstNameOperator = 3 AND T.FirstName <> TMP.FirstNameValue )
    OR
           ( TMP.FirstNameOperator = 4 AND T.FirstName LIKE TMP.FirstNameValue + "%")
    OR
           ( TMP.FirstNameOperator = 5 AND T.FirstName LIKE "%" + TMP.FirstNameValue )
    OR
           ( TMP.FirstNameOperator = 6 AND T.FirstName LIKE "%" + TMP.FirstNameValue + "%" )
    )
   
   AND
   
   (
           ( TMP.LastNameOperator = -1 )
    OR
           ( TMP.LastNameOperator = 0 AND T.LastName IS NULL )
    OR
           ( TMP.LastNameOperator = 1 AND T.LastName IS NOT NULL )
    OR
           ( TMP.LastNameOperator = 2 AND T.LastName = TMP.LastNameValue )
    OR
           ( TMP.LastNameOperator = 3 AND T.LastName <> TMP.LastNameValue )
    OR
           ( TMP.LastNameOperator = 4 AND T.LastName LIKE TMP.LastNameValue + "%")
    OR
           ( TMP.LastNameOperator = 5 AND T.LastName LIKE "%" + TMP.LastNameValue )
    OR
           ( TMP.LastNameOperator = 6 AND T.LastName LIKE "%" + TMP.LastNameValue + "%" )
    )
   
   AND
   
   (
           ( TMP.DateOfBirthOperator = -1 )
    OR
           ( TMP.DateOfBirthOperator = 0 AND T.DateOfBirth IS NULL )
    OR
           ( TMP.DateOfBirthOperator = 1 AND T.DateOfBirth IS NOT NULL )
    OR
           ( TMP.DateOfBirthOperator = 2 AND T.DateOfBirth = TMP.DateOfBirthValue )
    OR
           ( TMP.DateOfBirthOperator = 3 AND T.DateOfBirth <> TMP.DateOfBirthValue )
    OR
           ( TMP.DateOfBirthOperator = 7 AND T.DateOfBirth > TMP.DateOfBirthValue )
    OR
           ( TMP.DateOfBirthOperator = 8 AND T.DateOfBirth >= TMP.DateOfBirthValue )
    OR
           ( TMP.DateOfBirthOperator = 9 AND T.DateOfBirth < TMP.DateOfBirthValue )
    OR
           ( TMP.DateOfBirthOperator = 10 AND T.DateOfBirth <= TMP.DateOfBirthValue )
   
   )
   
   AND
    1 = 1
    )
    ORDER BY
           CASE WHEN @sortColumn = "FirstName" AND @sortOrder = "ASC"
          THEN T.FirstName END ASC,
           CASE WHEN @sortColumn = "FirstName" AND @sortOrder = "DESC"
          THEN T.FirstName END DESC ,
   
          CASE WHEN @sortColumn = "LastName" AND @sortOrder = "ASC"
          THEN T.LastName END ASC,
           CASE WHEN @sortColumn = "LastName" AND @sortOrder = "DESC"
          THEN T.LastName END DESC ,
   
          CASE WHEN @sortColumn = "DateOfBirth" AND @sortOrder = "ASC"
          THEN T.DateOfBirth END ASC,
           CASE WHEN @sortColumn = "DateOfBirth" AND @sortOrder = "DESC"
          THEN T.DateOfBirth END DESC
   
   
   DECLARE @count INT  
   SET @count = 0  
   SELECT @count = MAX(RowNum) FROM #FinalResults
   IF @startInd  > @count  
   BEGIN  
          DECLARE @numOfPages INT   
          SET @numOfPages = @count / @pageSize  
          IF @count % @pageSize > 1  
          BEGIN  
                 SET @numOfPages = @numOfPages + 1  
          END  
          SET @startInd = ((@numOfPages - 1) * @pageSize) + 1  
          SET @endInd = @numOfPages * @pageSize  
   END
   
   SELECT @count AS TotalRows, * FROM #FinalResults WHERE RowNum BETWEEN @startInd AND @endInd
   
   DROP TABLE #tmpForWhereClause
   DROP TABLE #FinalResults
   END
   

   
   
   
4. Update the text that reads - <procedure> or <table> in the above SQL script as you see fit.
5. Execute the stored procedure on the database and test once to ensure that it works :)