Implementing sorting in SQL query without using dynamic query.

In this article we will learn how to sort the table values with out using dynamic query to improve performance.

CREATE PROCEDURE SortingExample
(
@sortFiled INT,
@sortType INT -- 1 for ASC, 2 for DESC
)
AS
BEGIN
-- Create a table variable to store user data
DECLARE @myTable TABLE
(
UserID INT IDENTITY(1,1),
UserName VARCHAR(50),
Password VARCHAR(50),
Email VARCHAR(50)
)

-- Insert some data to table to work on that data

INSERT INTO @myTable(UserName, Password, Email)
VALUES ('Jack', 'JackPwd', 'jack@gmail.com')

INSERT INTO @myTable(UserName, Password, Email)
VALUES ('Anand', 'AnandPwd', 'raj@gmail.com')

INSERT INTO @myTable(UserName, Password, Email)
VALUES ('smith', 'smithPwd', 'smith@gmail.com')

INSERT INTO @myTable(UserName, Password, Email)
VALUES ('Brandy', 'BrandyPwd', 'tom@gmail.com')

-- If @sortType = 1 then sort the selected field in ASC order
-- Otherwise sort the selected field in DESC order
IF (@sortType = 1)
BEGIN
SELECT UserID, UserName, Password, Email FROM @myTable ORDER BY
-- To sort numeric field we have to convert int field into varchar field
CASE @sortFiled WHEN 1 THEN RIGHT(('000000000000000000' + CONVERT(VARCHAR(30), UserID)),18)
WHEN 2 THEN UserName
WHEN 3 THEN Password
END ASC
END
ELSE
BEGIN
SELECT UserID, UserName, Password, Email FROM @myTable ORDER BY
CASE @sortFiled WHEN 1 THEN RIGHT(('000000000000000000' + CONVERT(VARCHAR(30), UserID)),18)
-- To sort numeric field we have to convert int field into varchar field
WHEN 2 THEN UserName
WHEN 3 THEN Password
END DESC
END
END


See the pics below for the output of above query.

-- Sorting numeric column (UserID)
EXEC SortingExample 1, 1 -- Sorting UserID column in ASSENDING
EXEC SortingExample 1, 2 -- Sorting UserID column in DESENDING




-- Sorting varchar column(UserName)
EXEC SortingExample 2, 1 -- Sorting UserName column in ASSENDING
EXEC SortingExample 2, 2 -- Sorting UserName column in DESENDING



By Cos mos   Popularity  (1548 Views)