Dynamic SQL, Dynamic Ordering and Paging
Posted by decipherinfosys on April 22, 2008
In some of our previous posts, we have covered both Paging of recordsets as well as conditional ordering using the CASE statement in the Order By clause. In today’s post, we will address one of the questions that a reader had recently asked. She wanted to know how to support dynamic ordering in a procedure that does paging using one of the techniques that we had presented here (using the ROW_NUMBER() function).
Let’s create some sample tables first – her example used only 2 tables – a parent and a child so we will create along the same lines:
SET NOCOUNT ON
CREATE TABLE TAB1 (COL1 INT NOT NULL IDENTITY PRIMARY KEY, COL2 NVARCHAR(10));
CREATE TABLE TAB2 (COL3 INT NOT NULL IDENTITY PRIMARY KEY, COL4 NVARCHAR(10), COL5 NVARCHAR(10), COL6 INT, COL1 INT,
CONSTRAINT FK_TAB2_TO_TAB1 FOREIGN KEY (COL1) REFERENCES TAB1(COL1));
INSERT INTO TAB1 (COL2) VALUES (‘ABC’);
INSERT INTO TAB1 (COL2) VALUES (‘XYZ’);
INSERT INTO TAB1 (COL2) VALUES (‘ZEF’);
INSERT INTO TAB2 (COL4, COL5, COL6, COL1) VALUES (‘TEST1’, ‘APPLE’, 10, 1);
INSERT INTO TAB2 (COL4, COL5, COL6, COL1) VALUES (‘TEST2’, ‘GUAVA’, 20, 1);
INSERT INTO TAB2 (COL4, COL5, COL6, COL1) VALUES (‘TEST3’, ‘MANGO’, 30, 2);
INSERT INTO TAB2 (COL4, COL5, COL6, COL1) VALUES (‘TEST4’, ‘ORANGE’, 40, 2);
INSERT INTO TAB2 (COL4, COL5, COL6, COL1) VALUES (‘TEST5’, ‘PINEAPPLE’, 50, 3);
She wanted to pass in dynamic filter criteria as well as dynamic sort criteria and dynamic sort column. The way that they had done was by making use of dynamic SQL and then executing it using EXEC(). Not only is that bad for performance but this also can cause SQL injection issues. That does not mean though that this cannot be done in a better way by using dynamic SQL. One can make use of sp_executeSQL and follow the right rules in order to make it work and perform nicely. We would like to point you to Erland’s site where he has covered this in great detail (he is a MVP and the article is considered a bible on dynamic SQL usage in SQL Server). Read the conclusion as well where he mentions that dynamic SQL (if used correctly), can give the best performance.
This will also help you understand that dynamic SQL is not always bad which some people in the industry have adopted as a thumb rule. It is useful in scenarios when you have dynamic search conditions on multiple table joins and dynamic filter criterias on different tables.
Here are the URLs:
Now, coming back to our example, we will make use of the STATIC SQL trick that Erland has mentioned as well as make use of sp_executeSQL and show both approaches and in both of them, we will cover how to achieve dynamic ordering as well since that remains the same regardless of the approach.
Here is the SQL code for the STATIC SQL (we have created a stored procedure for this):
And here is the SQL code for the DYNAMIC SQL:
If you read Erland’s article, then understanding the flow of the code becomes easier so rather than going through the code line by line, we would recommend that you first read his article and then follow along. The key differences are that in these stored procedures, we also need to do paging and that is based off the input parameters for the starting record number and the max number of rows that need to be displayed for the page i.e. the page size. The code needs to return back only the specified sub-set of the rows and also needs to return the total count of the records that the actual query qualified so that one can display the total count (NOTE: There are other ways of doing this as well – read our post – one more paging records dilemma).
The STATIC code uses the (@X IS NOT NULL and COL5 = @X) trick in order to make sure that the performance is acceptable. It is easy to understand code and also allows easy usage. You do need to understand when to use this methodology. This search procedure meets all the three criterias which are necessary for going with the STATIC SQL route in this case:
a) Single Table search columns.
b) All columns are indexed.
c) Atleast one search condition needs to be provided for the filter condition.
The dynamic SQL code makes use of the system stored procedure SP_EXECUTESQL and uses a parameter list and then passes in the parameters into this stored procedure. That way, it makes use of parameterized queries and hence enables the plan re-use.
The dynamic ordering in the code is achieved by this portion of the code:
,ROW_NUMBER() OVER (ORDER BY
CASE WHEN @sortExpression = ‘COL4’ and @sortAscending = 0 THEN B.COL4 END DESC,
CASE WHEN @sortExpression = ‘COL5’ and @sortAscending = 0 THEN B.COL5 END DESC,
CASE WHEN @sortExpression = ‘COL6’ and @sortAscending = 0 THEN B.COL6 END DESC,
CASE WHEN @sortExpression = ‘COL2’ and @sortAscending = 0 THEN A.COL2 END DESC,
CASE WHEN @sortExpression IS NULL and @sortAscending = 0 THEN B.COL3 END DESC,
CASE WHEN @sortExpression = ‘COL4’ and @sortAscending = 0 THEN B.COL4 END ASC,
CASE WHEN @sortExpression = ‘COL5’ and @sortAscending = 1 THEN B.COL5 END ASC,
CASE WHEN @sortExpression = ‘COL6’ and @sortAscending = 1 THEN B.COL6 END ASC,
CASE WHEN @sortExpression = ‘COL2’ and @sortAscending = 1 THEN A.COL2 END ASC,
CASE WHEN @sortExpression IS NULL and @sortAscending = 1 THEN B.COL3 END ASC
) AS ROWNUM
Using this piece of code, we can avoid the data-type conversion that can happen when you have a single CASE statement. Also, we can just check for the sort expression and the sort order and then use the right column and order for the row numbering. The paging logic is based on the ROW_NUMBER() function like we have explained in some our paging posts before. Using either of the STATIC Or the DYNAMIC route, one can obtain the optimal performance as well as meet the functional requirements.
One Response to “Dynamic SQL, Dynamic Ordering and Paging”
Sorry, the comment form is closed at this time.