Whenever developer executes a query, it goes through following steps
1. Checking validity of Query
2. Creation of execution plan of Query
3. Actual execution of Query

If developer writes Stored Procedure, first 2 steps are already done while Stored Procedure creation. So Stored procedure is always faster than executing query in code behind. But due to some reason if developer writes “Dynamic Query” in Stored Procedure , then every time store procedure execution goes through all the above 3 steps .

Following are some of the scenarios in which developer writes Dynamic Query.

Scenarios:
1. When Input parameters for a store procedure are having multiple values [Comma Separated Values].
2. When Input parameters for a store procedure are having multiple values and Database table column on which data gets filtered is dynamically decided as per input parameter to stored Procedure.

Developer can avoid first scenario by using “XML” data type to create temporary table and then use this temporary table to filter multivalue data. For second scenario again developer can use “XML” data type, temporary table with “Case” statement to decide column at run time.

For any clarifications or for further information, please email us at info@iotap.com.