February 27, 2005 at 2:42 pm
Hi everybody
I have a query, which takes a lot of params (in the where clause) including a order by param. The result set I get is inserted in the table variable where depending upon the pageno, only certain amount of records are pulled.
I cannot use the case statement in order by because the datatypes are not the same. I do not want to use dynamic sql (exec(@stmt)). Can use the sp_executesql at the moment, but am worried, if some more columns are added, then this wouldn't work (will statement will be more than 4000 characters).
Any suggestions!!! Your help is greatly appreciated.
February 27, 2005 at 10:47 pm
use the ordinal position in your case statement. No dynamic sql required.
Set @order = 2
select empid, empname
from employees
order by case @order
when 1 then empid --(or 1)
when 2 then empname
when....end
February 28, 2005 at 2:11 am
Typically real world situations are far more complex. If that is the case see if this helps: http://www.sommarskog.se/dyn-search.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 28, 2005 at 7:42 am
I have a couple queries that I just got done rewriting that had the same issues. The original was all Dynamic SQL (dynamic columns, joins, where clause, and order by), the final version had none. The way I did the sort (I had about 15 sort methods possible) was I queried the core results unsorted into a temp table. I then set up an IF - ELSE IF structure to do the sort. Each condition did an INSERT INTO TempTable2 SELECT FROM TempTable1 ORDER BY specific conditions where only the order by clause varied. The down side to this is that if your final results have a large number of rows you replicate the data into a second temp table which could burn some time. In my case I had the ability to say that the users had no use for results returning more than 100 rows (due to too few query constraints) and was able to use ROWCOUNT to abort the original query and require the user to refine the search. Our objective was to provide usefull results, not to cater to lazy requests that would have just been ignored anyhow since the data they were after would likely be too far buried.
If your data size isn't too massive this approach allows the core query to be extremely complex (multi staged in my case) and allow an unlimited number of sort types without having to replicate any code or use dynamic SQL.
February 28, 2005 at 1:44 pm
Hi michanne,
Tried your solution, but this wouldn't work (or should I say didn't work) because it is throwing an error informing that it could not convert the varchar to int. If you have tried this please let us know (of course with the code).
I could convert the other datatypes to varchar, but then sorting is done through ASCII value and not by the desired data types.
To Araon Templeton
Thank you, because I think it is a good solution (especially in my case, because the query is too complicated).
To Frank Kalis
Thank you for the link. It was very useful as I learnt that even dynamic sql is cached if you used it right (sp_executesql with parameter list). You learn everyday!!!!
Thanks once again everybody for your advice.
February 28, 2005 at 1:57 pm
Regarding the error, I found that
Set @order = 2
select empid, empname
from employees
order by case @order
when 1 then empid --(or 1)
when 2 then empname
when....end
works when the actual columns receiving the "order by" are numeric, and provides the error when they are string based...strange.
February 28, 2005 at 2:11 pm
I'm not 100% sure about this. But from my experience it seems that all the expressions in the case must return the same datatype. So it's either all strings or all numbers. If you try casting the numbers to varchar you won't get that error... but obviously the sort order by be wronged since '10' is smaller than '2'.
February 28, 2005 at 2:15 pm
This is due to implicite type conversion. The order by expression has to resolve into a single "column" of some type just as if it were in the select portion. I believe SQL Server generally uses the first value's type as the prototype and tries to convert the remaining values to that type, but I could be wrong with those specifics. So, since empid is numeric it attempts to convert empname to a numeric type and produces the error. If you switched the order (by putting empname in case 1) then it would attempt to implicitely convert empid to a varchar and succeed. But as Anjali pointed out, numerics do not sort correctly as varchar unless you prepadd with leading zeros, keep a constant decimal position, and all that mess. It would be possible but would require a bit of work both to code it and then for SQL Server to perform the complex conversions. Granted, it would still be less work than completely replicating the table, but you get a combinatorial effect when you have multiple variable columns in the order by (subsorting 2-3 layers).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply