SQL query - Table sorting

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