For my current project I have a stored procedure that needs to sort data by 1 of 3 columns depending upon the option chosen by the user from the application. This is only the dynamic part of the stored procedure, which also happens to be very complicated, so I didn’t wish to use dynamic SQL Server. I know that within SQL server 2000 you can use a case against a variable to select which column to order the data by but the only caveat is that all the columns have to be the same data type. My problem was that not all my columns are the same data type, I managed to over come this by using the new function within SQL server 2005 call Row_Number(), which gives a row number to each row, based upon the order by used within the over function, as this always returns the same data type it can be used within the case statement for the ordering. Below is an example of code:
DECLARE @vOrderby AS INT
SET @vOrderby = 4
SELECT TABLE_CATALOG , TABLE_SCHEMA , TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE
FROM information_schema.columns
ORDER BY CASE @vOrderby
WHEN 1 THEN ROW_NUMBER() OVER(ORDER BY TABLE_SCHEMA, TABLE_NAME)
WHEN 2 THEN ROW_NUMBER() OVER(ORDER BY COLUMN_NAME)
WHEN 3 THEN ROW_NUMBER() OVER(ORDER BY DATA_TYPE)
WHEN 4 THEN ROW_NUMBER() OVER(ORDER BY ORDINAL_POSITION)
WHEN 5 THEN ROW_NUMBER() OVER(ORDER BY IS_NULLABLE)
WHEN -1 THEN ROW_NUMBER() OVER(ORDER BY TABLE_SCHEMA DESC , TABLE_NAME DESC )
WHEN -2 THEN ROW_NUMBER() OVER(ORDER BY COLUMN_NAME DESC )
WHEN -3 THEN ROW_NUMBER() OVER(ORDER BY DATA_TYPE DESC )
WHEN -4 THEN ROW_NUMBER() OVER(ORDER BY ORDINAL_POSITION DESC )
WHEN -5 THEN ROW_NUMBER() OVER(ORDER BY IS_NULLABLE DESC)
END
I am not recommending this as a good solution, just a solution that might be useful for a small database.