About Quering

After so many thoughts, about quering, finally i decided to use store procedures, at the end there's no fast stuff, although is an structured way sometimes speed is needed, this doesn't mean that i will code business rules, it just mean that i will query

So, this is about quering, but not an "select * from persons", that is for teaching... Most of the projects i work are implemented in Sql Server 2005, and there's always a requirement... "i need to page results... i need to sort results... i need multiple criteria..." and the worst "i need all of them".

Well there's always a solution, but sincerely, i couldn find a good solution, all of those solutions throw away the advantage of a compiled store procedure cause they use dinamic query construction. And that's no right, it broke principles of authorization and ends with an database with free access to tables.

Well, this is a partial soluiton. The one i solve today is the criteria query, this requirement implies that you have to provide the user with the option to send a parameter with the value if he wants to filter, or he can send null if he doesnt want to filter, and can happen same with several parameters. The solution is a trick:



select ....
from ....
where EmployeeId = isnull(@EmployeeId, EmployeeName) or @EmployeeId is null

This solution allows to the stored procedure to be compiled, and result in aceptable performante, what is the first goal, next we will have to paginate results in order to avoid network floods.

The other thing i solve is the pagination problem, although i have my doubts of performance, my tables dont exeeds a one millon of records in a indexed fields, and thats temporally ok, the downgrade of this solution increase at the last pages, this is:

select ....
from (
select ...., row_number() over (order by ....) as row
from ....
where EmployeeId = isnull(@EmployeeId, EmployeeName) or @EmployeeId is null ) as QueryResult
where row between .... and ....


Finally, the ordering problem, if you have a burned fields to order is perfect you dont have to do anymore, but if you want to pass the ordering field as a parameter, thats where i cant help.... cause theres the time to generate dinamic query.