Two months ago i write about real quering, trying to solve 3 issues in a single store procedure for SqlServer 2005: Dinamic criteria, dinamic paging and dinamic ordering. The solution i give last time doesnt contemplate using ordering, but i finally got it. I really dont know why no one have published something like this, cause i spend some hours around internet, in fact it makes me think i havent seen something, but from my point of view it works well.
Why i bother spending some time looking for this answer, if some post show the easy solution: generate the sentence dinamically inside the procedure, well... two smasing reasons... security and speed. And if you think, what others reasons do you have to make store procedures?, maybe none. So if you make the sentence dinamically you have to give permissions to the tables... no good, and therefore you lose speed cause SqlServer have compile the sentence each time it excecute... no good again.