SQL Strings vs. Conditional SQL Statements

Question

Is there an advantage to piecemealing sql strings together vs conditional sql statements in SQL Server itself? I have only about 10 months of SQL experience, so I could be speaking out of pure ignorance here.

Where I work, I see people building entire queries in strings and concatenating strings together depending on conditions. For example:

Set @sql = 'Select column1, column2 from Table 1 ' If SomeCondtion @sql = @sql + 'where column3 = ' + @param1 else @sql = @sql + 'where column4 = ' + @param2 

That's a real simple example, but what I'm seeing here is multiple joins and huge queries built from strings and then executed. Some of them even write out what's basically a function to execute, including Declare statements, variables, etc. Is there an advantage to doing it this way when you could do it with just conditions in the sql itself? To me, it seems a lot harder to debug, change and even write vs adding cases, if-elses or additional where parameters to branch the query.

Answer 1

Dynamic SQL is used in situations where the conditions aren't known ahead of time. One example I've run into often is an "advanced" search form where the user can search on multiple fields and can search beyond simple "substring" searches where they can specify a search value must match exactly (OR match as a substring, and case sensitivity is an option), and for numeric/date values they can specify operators >, <, >=, <=. It might also happen that one search form is actually searching multiple joined tables. In this case, it's much easier to build a query on the fly as a string and execute it that way, than to try to imagine every possible query they user could submit and have if-else blocks choose a pre-existing query.

You are correct though. Dynamic SQL can be very difficult to debug (when debugging, I add code to print the query with and without the substitution values before executing it) and can also run slower.

Answer 2

First, string concatenation is a dangerous practice. In theory, it's fine if you're concatenating parameterized strings like:

sql += "where foo = @param"; ... command["@param"] = <some value>; 

Otherwise, it's prone to SQL injection attacks.

But more to the point, the answer is the dreaded, "it depends". A huge query can be very fast if the database tables are indexed for those scenarios. On the other hand, often times the client understands the conditions and the subset of data it needs better than the database server. In the example you gave, it's actually reasonable that the client customizes its WHERE clause before executing it.

Having the client customize its query and extracting the data it needs from it can distribute work better, but again, it depends on what the tables are designed for. On one extreme, some schemas are designed to be a la carte, performing best with many little ad-hoc queries against individual tables. Other schemas are designed to expose only a limited set of highly optimized stored procs, from which one should not stray. In the latter case, you'd be right - they should go with larger queries. In the former case, it's entirely reasonable for the client to take its share of the decision making.

Answer 3

Sounds like you're working with a bunch of Spaghetti Code. No reason the building of sql strings can't be broken down into structured and more managable pieces. It probably comes down to do you prefer your programming language or tsql. Personally, I think building sql statements in code is always a mess. In your case, it doesn't look like using an ORM is an option unless you can convince the rest of the team/those in charge.

I'm not sure one pre-written, self-contained SQL statement with all the conditions is any easier to debug than several simpler statements generated by coded conditions in your language of choice. Either one can be done poorly.

EDIT: are you proposing something like this?

Select column1, column2 from Table 1 where (SomeCondtion = 0 and column3 = @param1) or (SomeCondition = 1 and column4 = @param2) 

Answer 4

Classic example of needing a Builder pattern

Try to build up a SQL query as the result of a fluent interface. Hibernate for Java does it like this for their Criteria API:

List cats = sess.createCriteria(Cat.class) .add( Restrictions.in( "name", new String[] { "Fritz", "Izi", "Pk" } ) ) .add( Restrictions.disjunction() .add( Restrictions.isNull("age") ) .add( Restrictions.eq("age", new Integer(0) ) ) .add( Restrictions.eq("age", new Integer(1) ) ) .add( Restrictions.eq("age", new Integer(2) ) ) ) ) .list(); 

Of course, their approach is a generic SQL solution, your builder could be targeted at precisely one query type that needed dynamic construction at runtime.

Answer 5

I've actually built queries like that myself in the past when SomeCondition is not something that SQL server could/should possibly know

For example, if SomeCondition is a check to see if a CheckBox is checked or not, then there is no way SQL server could or should know that

It was also because I wasn't comfortable creating stored procedures in SQL, however since then I've gotten much more comfortable with SQL Server and would now create a stored procedure that accepts an IsChecked parameter. It's much easier to maintain and debug, and the risks of a sql injection attack are lower.

There are many developers who are not comfortable with building stored procedures in SQL, and to them, its faster/easier to use the programming language to build in their conditions instead of building a stored procedure in SQL Server.