Which is Faster Static SQL or Dynamic SQL inside a Procedure?
Posted on 2007-12-03
I'd like to know which method is better (in terms of perfomance) for developing a procedure.
I have a procedure which accepts a number of paramters and depending upon the parameters the filter condition is to be applied. The 2 ways of developing are
1. Declare a temporary VARCHAR variable and dynamically create the sql
E.g ssql = "SELECT col1, col2, col3 FROM table1, table2 WHERE table1. col1 = table2.col1"
IF NOT ISNULL(parameter1) THEN
ssql = ssql + " AND table1.col1 IN (" + parameter1 + ")"
2. Write the SQL statically
SELECT col1, col2, col3 FROM table1, table2
WHERE table1. col1 = table2.col1
AND (ISNULL(parameter1) OR table1.col1 IN (parameter1))
Which of this method is more efficient? Does the ISNULL keyword prevent the DB engine from creating the plans/using the proper index?
Expecting a detailed explanation.
Thanks in advance.