Database - Stored Procs: How can I add parameters as apt, without building a string?
There are often cases where Stored Procedures have parameters that are optional, which means that you then need to either have multiple SQL statements (which makes maintenance hard!), or build a single SQL statement as apt.
In many cases people go for the single statement option, but do it by building a string containing the SQL statement, and then run that. This is far from ideal, as it is more prone to errors/bugs (due adding too many quotes, not sanitising values, etc), eliminates any colour coding that the editor gives you (which increases the chances of errors!), and makes it run more slowly (as the database engine cannot optimise your query, due to the entire thing effectively being dynamic).
There is a fairly easy solution - which is to deal with the conditions inside the SQL statement itself.
Assuming that your current code for dealing with a parameter is like this:
Code:
IF @Acct_Code IS NOT NULL
SELECT @sql = @sql + ' AND Acct_Code = ''' + @Acct_Code + ''''
..you can put the same thing directly into the SQL statement by doing this:
Code:
AND ((@Acct_Code IS NULL) OR (Acct_Code = @Acct_Code))
There are now two conditions for the parameter in the SQL statement, but the amount of work done is the same as before.
The first condition will only be checked once for the entire query (just as the If statement was), because the value is fixed at the time the query starts - it does not change for each row.
When the first condition is True, the second will not be checked at all - because the result of an Or will be True if either or both parts are True (ie: because the first condition is True, it doesn't matter whether or not the second condition is)
When the first condition is False, the second condition will then be tested in the same way as it would have been if you had added it in the original way.
There are two important things to remember for this method:
- The first condition needs to be the opposite of what the If statement was - in the example the If was checking "Is Not Null", so the condition checks "Is Null".
- The brackets on each end are important, as they ensure that the two conditions are treated as two parts of the same condition - and do not interfere with other parts of the query.
