Jeremiah V. writes to share with us another snippet fact: there is always a longer way to do something, no matter what language is used. In SQL-92 compatible databases, it is a challenge to have a dynamically-generated ORDER BY clause, but there are a number of ways to produce a reasonable implementation. For instance, a quick Google search turns up using a combination of CASE and CONVERT to handle a dynamic ORDER BY statement with different column types.

Jeremiah found the much, much longer version of a solution to this problem. Once he stopped laughing, he notes for us that many, many permutations are missing, save for some of the ORDER BY criteria being treated as a single unit. The CASE and CONVERT functions are avoided in this implementation, but the code size becomes enormous... let's hope they don't add a new column any time soon:

IF @OrderByCriteria LIKE 'receipt_date, Account_Number, major_version, minor_version, Vendor_ID'       
        SELECT * FROM @Table_Input ORDER BY receipt_date, Account_Number, major_version, minor_version, Vendor_ID

ELSE IF @OrderByCriteria LIKE 'receipt_date, Account_Number, Vendor_ID, major_version, minor_version'       
        SELECT * FROM @Table_Input ORDER BY receipt_date, Account_Number, Vendor_ID, major_version, minor_version

ELSE IF @OrderByCriteria LIKE 'receipt_date, major_version, minor_version, Account_Number, Vendor_ID'   
        SELECT * FROM @Table_Input ORDER BY receipt_date, major_version, minor_version, Account_Number, Vendor_ID

ELSE IF @OrderByCriteria LIKE 'receipt_date, major_version, minor_version, Vendor_ID, Account_Number'   
        SELECT * FROM @Table_Input ORDER BY receipt_date, major_version, minor_version, Vendor_ID, Account_Number

ELSE IF @OrderByCriteria LIKE 'receipt_date, Vendor_ID, Account_Number, major_version, minor_version'       
        SELECT * FROM @Table_Input ORDER BY receipt_date, Vendor_ID, Account_Number, major_version, minor_version

ELSE IF @OrderByCriteria LIKE 'receipt_date, Vendor_ID, major_version, minor_version, Account_Number'       
        SELECT * FROM @Table_Input ORDER BY receipt_date, Vendor_ID, major_version, minor_version, Account_Number

ELSE IF @OrderByCriteria LIKE 'Account_Number, receipt_date, major_version, minor_version, Vendor_ID'       
        SELECT * FROM @Table_Input ORDER BY Account_Number, receipt_date, major_version, minor_version, Vendor_ID

ELSE IF @OrderByCriteria LIKE 'Account_Number, receipt_date, Vendor_ID, major_version, minor_version'       
        ... (original snippet continues in this manner)

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!