Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
    
So they're using Dynamic SQL inside of, what, stored procs? –  Scott Whitlock May 31 '12 at 15:23
    
This is in stored procs, @ScottWhitlock. They are building the query string dynamically within the stored proc itself. –  Yatrix May 31 '12 at 15:38
1  
SQL Server isn't going to consider a fully formed select statement (with or without parameterized values) sent to it as dynamic. –  JeffO May 31 '12 at 15:49
add comment

5 Answers

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.

share|improve this answer
    
Oh, I understand the need, but some of them are cases where this isn't the case. I didn't know if there was a good reason to pick one over the other when either would work. –  Yatrix May 31 '12 at 15:34
2  
@Yatrix: I try to avoid it as much as possible. As I said, it's the better choice when the query can only be known at runtime, or when enumerating all possible queries is not really feasible (I admit that for "simple" search forms, it might be better to have 2 or 3 if-else statements to choose the right query, but that rarely seems to happen to me). –  FrustratedWithFormsDesigner May 31 '12 at 15:35
add comment

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.

share|improve this answer
1  
+1 for the SQL injection risk. You should always use parametrized queries. –  Matthew Flynn May 31 '12 at 15:53
add comment

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) 
share|improve this answer
    
+1 Using some kind of fluent Builder pattern interface is much better than concatenating arcane strings of SQL. –  Gary Rowe May 31 '12 at 15:47
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
I don't think a stored procedure necessarily solves this problem. You can have a bunch of convoluted t-sql flow-control statements that build a dynamic sql statement or handle everything in a single select statement using the IsChecked parameter. –  JeffO May 31 '12 at 19:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.