Many times we would run into situation where user would want to select data limited by a criteria, or “all”. For example, let’s say we build a UI where user can list all the users by a certain State or all States.
IF @State = 'ALL' BEGIN SELECT * FROM User END ELSE BEGIN SELECT * FROM User WHERE State = @State END
- Poor code reusability. The first part of SQL is redudant in both conditional branches (SELECT * FROM User). This is fine when the queries are simple. I have seen some complex SQL queries that are miles long, this means developers would have to copy the complex select statement a few places.
- With there is only one condition, we only need one branch. However, what if we have a complex query condition, such as gender (Male, Female or All), department (certain department, or all), status (full-time, part-time, contractor or all), etc … You can see the query can grow exponentially complicated. So in a case where customer requests an “Advanced Search” feature with 5 criteria, we can look at potentially 2 ^ 5 = 32 if branches for the SQL query.
SELECT * FROM User WHERE (@State = ‘ALL’ OR State = @State)
SELECT * FROM User WHERE (@State = ‘ALL’ OR State = @State) AND (@Department = ‘ALL’ OR Department = @Department) AND (@State = ‘ALL’ OR Status = @Status)