Monday, December 10, 2012

Fun with SQL Server and Quotes

I am trying to help with some SQL and we were really having fun today! A complicated dynamic SQL statement involving cursors was failing with the illuminating error: syntax error near ‘a’. When working with SQL Server it is not easy to parameterize for table names, hence the dynamic SQL instead of normal static SQL. Well, the problem turned out to be a quote in a name in the database. This was causing the generated SQL statement to be incorrect.

The best would be to fix all SQL queries, but that was not feasible right now. Instead we tried to replace all quotes by two quotes. (This was a staging table anyway, so no problem in polluting this table: it is not used by others). Sometimes you have to be pragmatic and take a short cut. The static SQL to do: replace a single quote by two single quotes looks already funky but the dynamic SQL version is really horrific: