Click here to Skip to main content
15,071,899 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All,

I need some suggestion regarding choosing between standalone query and stored procedure.

currently from my web application i am executing some queries during runtime based on the user selection i am framing where condition and executing the SQL queries.

now i want to avoid this by sending the whole query with wherecondtion to a storeprocedure as a input parameter and execute it inside SP and send back the results back as a refcursor.

but here i have a question is there any difference between executing a query directly in the application and sending the query as a input to SP and getting back the results.

i know oneofthe benefit with the SP is the program will be compiled once.
but i want to send the query as input parameter
v_select:= "select * from xyz where a=33 and b=',...'"

open ref_cursor for v_select //here again i think the query will be recompiled rite?

is there any difference between the above 2 scenarios...plese suggest me which one is better one.
Posted

Would be very dangerous sending the entire query to a stored procedure, as Im sure this could open you up for sql injection.

And you also lose the benefit of sending less data down the wire. As you are using dynamic cursors these would be compiled at run time.

Your best bet would be to create a database view and with the bulk of the logic and then just add the additional parameters to a where clause that are needed by your application and query the view.
   
Hi,

You can add all condition in where clouse like this,

where 
(LENGTH(m_Value1) = 0 OR (MT.ColumnNo = m_Value1))
and (LENGTH(m_Value2) = 0 OR (MT.ColumnNo1 = m_Value2));


this exp with mysql , please take as a concept only.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900