T-SQL ORDER BY conditionally

Question

I am writing some kind of resource management system.

A resource is an example of a definition. Definition is metadata, they mainly contain properties.

This is generally my DB:

TypeDefinition id name =============== 1 CPU PropertyDefinition id name typeDefinitionId valueType ================================================ 1 frequency 1 int 2 status 1 string TypeInstance id name typeDefinitionId ================================= 1 CPU#1 1 2 CPU#2 1 PropertyInstanceValue id propertyDefinitionId typeInstanceId valueType intValue StringValue FloatValue ======================================================================================== 1 1 1 int 10 2 2 1 string Pending 3 1 2 int 20 4 2 2 string Approved 

DEMAND:

order all resources according to the value of a specific property .

For example: order all resources according to their status → The value of CPU # 2 will appear before CPU # 1, because "Approved" before "Pending".

If we ordered by frequency , CPU # 1 will appear in front of CPU # 2, because 10 to 20.

Therefore, I need to sort each time according to a different column (intValue / stringValue / FloatValue / etc), depending on the valueType property.

Any suggestion?

LIMITATION:

PIVOT is currently the only option we thought about, but it really is not, since the database is huge, and I need the request to be as fast as possible.

Thank you very much in advance,

Michal.

Answer 1

If the problem is that you do not want to dynamically build the query, use this order by structure:

 order by case @orderby when 'status' then status when 'frequency' then frequency end option (recompile) 

You will pass the @orderby parameter. The final recompile option should force the engine to build a new plan in accordance with the parameters passed, that is, provided that you use the stored procedure.

Answer 2

If you want to order the results of your query using SQL (as opposed to sorting them in the calling application after returning the results), you will need to create Dynamic SQL and execute it using sp_executesql.

http://msdn.microsoft.com/en-us/library/ms188001.aspx

Answer 3

If I understood your question correctly, I would approach it as follows:

• Create a table of valid string values ​​by including a column to indicate the sort priority (let this AllowedValues)

  create table [dbo]. [AllowedStringValues] (PropertyDefinitionId int, stringValue varchar (250), sortOrder int)

• Create a complex subquery that selects the correct value based on the PropertyDefinition row (it looks like it should look among 3 columns depending on what data type it is).

• If the value is a string type, the inner join of the subquery with the AllowedStringValues ​​table (assuming the value was stored in the value column)

  AllowedStringValues ​​inner join in ValueType = String AND value = stringValue OR ValueType <> string

• Sort by sort priority in AllowedValues ​​if it is a string or a numeric value otherwise.

  sequence number ValueType when "string" then sortOrder else value end