Dynamic SQL Server ordering without using dynamic SQL.
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: 1: DECLARE @vOrderby AS INT 2: SET @vOrderby = 4 3: 4: SELECT TABLE_CATALOG , TABLE_SCHEMA , TABLE_NA