I have an SP of numerous lines and I want to filter its resultset. I'm confused which way would be good -- does performing database specific operations in code look feasible? Or is there any other option here? Do I just deal with the convoluted SP and edit it?
I'm thinking to do some SQL like processing in code. What would you do if this was your problem?
What do you mean by "SQL processing in code"? SQL processing is generally better done by the database, mostly for performance reasons (the database is closer to the data, so it avoids the network traffic, plus databases are designed to do SQL processing right from the start, so are usually pretty good at it.
Generally the possibilities I see (in no particular order) are:
1) Get rid of the stored procedure and code the logic in the application. I might opt for this if there was a long-term outlook to get rid of all stored procedures and the procedure wasn't actually needed for performance.
2) Fix the stored procedure (perhaps overhaul the DB code a bit). If there is lots of database code, and you don't plan to get rid of it for any reason, then it would be better to keep it in shape.
3) Some databases might allow you to use the SP (perhaps after converting it to a function that returns a resultset) in an SQL query as a table, so you might be able to do the SQL operations over the result of the SP itself. It might solve your needs, but still looks like a little bit of hack. Quick fix for a project that doesn't have a longer term development perspective.
What I meant by database processing in code was that to perform operations (like remove) in Java after I fetch the records. To give an example, I want to remove all invalid telephone numbers that have bypassed the form validation.
The third possibility mentioned above looks like it will come in handy. Any way MySQL supports this? Searching the internet did not give any relevant results.
I'm not at all versed in MySQL, but it seems that MySQL cannot do this. You might modify your stored procedure to create a temporary table and then use SQL over that table to do the processing you want. The question is whether it is not better to fix the stored procedure outright, given that you're going to touch it anyway.