Search This Blog & Web

Friday, July 17, 2009

How to avoid dynamic query using procedures

We all know string query or dynamic query utalize so much resources while execution. Major flow back of dynamic query is string conversion of all types of parameters and writing bad code through SQL injection.

Here is a solution to avoid that type of query.

Case:
There is a column "Param" and it is not required every time while query execution.
the table joins with master table as left outer join.

Problem:
When we provide parameter we can use case to handle it without writing dynamic query. But when we do not provide parameter value then only child table records return what can i do to return whole dataset.

Solution:
here is the solution

DECLARE @PARAM INT
SET @PARAM = 0

SELECT * FROM Master LEFT OUTER JOIN Child ON Master.id = Child.id
WHERE (CASE WHEN @PARAM = 0 THEN master.id ELSE child.id END )
IN (CASE WHEN @PARAM = 0 THEN master.id ELSE
CASE WHEN @PARAM > 0 THEN @PARAM ELSE child.id END
END)


when we provide paramter then only required records returns else all records returns from master table and we do not need any dynamic query.
Post a Comment