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
    @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.

  • So they're using Dynamic SQL inside of, what, stored procs? May 31, 2012 at 15:23
  • This is in stored procs, @ScottWhitlock. They are building the query string dynamically within the stored proc itself.
    – Yatrix
    May 31, 2012 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, 2012 at 15:49

5 Answers 5


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.

  • 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, 2012 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). May 31, 2012 at 15:35

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.

  • 1
    +1 for the SQL injection risk. You should always use parametrized queries. May 31, 2012 at 15:53

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) 
  • +1 Using some kind of fluent Builder pattern interface is much better than concatenating arcane strings of SQL.
    – Gary Rowe
    May 31, 2012 at 15:47

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) ) )
    ) )

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.


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.

  • 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, 2012 at 19:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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