The best place to sort data from your database is not on your SQL Server. There are times when you have no choice. So, now you have to sort your results from a query. Your DBA says you can’t use dynamic SQL. You are only allowed to use stored procedures. Now What do you do?
Today I am demonstrating a stored procedure technique allowing you to sort by different criteran. Each possible permutation has to be coded, but it does allow you to order the data differently from one query to the next. In fact, this could be used to group data differently as well.
To demonstrate this technique I’m going to create a small table in tempdb for you to test on your own.
USE TempDB
GO
CREATE TABLE SortDemo
(
Id int NOT NULL IDENTITY(1,1)
,PetType VARCHAR(128) NOT NULL
,Surname VARCHAR(128) NOT NULL
,City VARCHAR(128) NOT NULL
,Country VARCHAR(128) NOT NULL
)
GO
INSERT INTO SortDemo (PetType, Surname, City, Country)
VALUES
('Dog', 'Brown', 'Florida', 'USA')
,('Tiger', 'Ravichandran', 'Bangladesh', 'India')
,('Parrot', 'Delgado', 'Santiago', 'Chile')
,('Tropical Fish', 'Taylor', 'Appleton, WI', 'USA')
GO
Now that you have some data, let’s demonstrate one way to write your dynamic sorting stored procedure.
CREATE PROCEDURE prsDemoProedureWithSort
(
@SourtColumn INT = 1
)
AS
-- Enums
DECLARE @ID INT = 1
DECLARE @PetType INT = 2
DECLARE @SurName INT = 3
DECLARE @City INT = 4
DECLARE @Country INT = 5
DECLARE @Composite INT = 6
SELECT
ID
,PetType
,SurName
,City
,Country
,CASE @SourtColumn
WHEN @ID THEN ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY ID)
WHEN @PetType THEN ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY PetType)
WHEN @SurName THEN ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY Surname)
WHEN @City THEN ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY City)
WHEN @Country THEN ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY Country)
WHEN @Composite THEN ROW_NUMBER() OVER
(PARTITION BY 1 ORDER BY Country, City, Surname) END
AS DisplayOrder
FROM SortDemo
ORDER BY DisplayOrder
GO
What happens behind the scenes is very interesting. If you show the execution plan for this query, all six of the ordering expressions are computed, while only the one selected is used for the DisplayOrder variable. That means, for every ROW_NUMBER() formula you include in this case statement, you increase the amount of effort necessary to return data.
Try executing the procedure with each of the options. You’ll see that it uses a different sort pattern each time.
EXEC prsDemoProedureWithSort 1
EXEC prsDemoProedureWithSort 2
EXEC prsDemoProedureWithSort 3
EXEC prsDemoProedureWithSort 4
EXEC prsDemoProedureWithSort 5
EXEC prsDemoProedureWithSort 6
Tomorrow we’ll look at another example that takes more work, but doesn’t carry such a heavy price in resource utilitzation.
Cheers,
Ben