Wednesday 29 January 2014

TSQL IN clause Generator

The below class, generates TSQL IN Clauses. Basically, you'd need them when you'd have a large list of items and you'd like to pass to a SELECT statement in Oracle, SQL, ODBC or any other database.

For example, Oracle 10g has got a limitation for 1000 items in the IN clause and here is how you can get around it:

   public interface ISqlInClauseGenerator
    {
        string Generate(string[] items, string columnName, int inClauseLimit = 1000);
    }

    public class SqlInClauseGenerator : ISqlInClauseGenerator
    {
        const string EndClause = ")";
        const string StartClausePattern = "{0} IN (";
        const string ConnectorClausePattern = ") OR " + StartClausePattern;
           
        public string Generate(string[] items, string columnName, int inClauseLimit = 1000)
        {
            if (items.IsNullOrEmpty())
            {
                return string.Empty;
            }

            string startClause = string.Format(StartClausePattern, columnName);
            string connectorClause = string.Format(ConnectorClausePattern, columnName);

            var sb = new StringBuilder(startClause);

            for (int i = 0; i < items.Length; i++)
            {
                if (i == 0)
                {
                    // start
                    sb.Append(items[i]);
                }
                else if (i % inClauseLimit == 0)
                {
                    // start a new IN clause
                    sb.Append(connectorClause);
                    sb.Append(items[i]);
                }
                else
                {
                    // continue
                    sb.Append("," + items[i]);
                }

                if (i == items.Length - 1)
                {
                    // it's the last item
                    sb.Append(EndClause);
                }
            }

            return sb.ToString();
        }

    }

For example, if you have a list of 2000 productIds, this will generate something like this:
"ProductId IN (1,2,3...1000) OR ProductId IN (1001...2000)"