SQL Trivia: Leading spaces on string field

By Steve Endow

I think I've been writing SQL statements since 1996.  Maybe I encountered this scenario many years ago, but completely forgot about it.  But I am pretty sure that since I have been working with GP over the last 13 years, I can't remember ever encountering it.

During the SmartConnect training class that I attended this week, the trainer, Mark Anderson, pointed out a situation where an extra space could accidentally make its way into a SQL statement in a SmartConnect map.  He explained that the leading space in the WHERE clause value would cause the query to return zero records.

Somehow, I don't think I've run into this problem--I just haven't made that particular typo.

Here's an example of what that might look like.  The first query has no leading space in the WHERE clause, but does have trailing spaces, and it retrieves one customer record.  But the second query adds a few spaces in front of the customer ID value in the WHERE clause, and that query returns zero records.

Most Dynamics GP application fields do not seem to allow leading spaces--the only exception I could find is the Note field/window.  Given this, it is unlikely that you would have a situation where Dynamics GP data would have a leading space and cause this scenario.

However, if you have integrations or reporting with custom queries, or queries that are concatenated through script or code, it's possible for a space to creep in.  In SmartConnect, this can occur when you are inserting a pre-defined data field name into a SQL statement, like this:

SELECT * FROM RM00101 WHERE CUSTNMBR = ' _CUSTOMERID'

When you drag the _CUSTOMERID source data field name into your SQL script, it can result in an extra space before the field name, causing the SQL statement to return zero records.

Since I can't remember having ever encountered this scenario, it isn't something I would think to look for.  It makes sense, but it wouldn't have been obvious to me.  I've become almost indifferent to trailing spaces, because they have no consequence with a typical GP SQL install, but leading spaces are definitely a gotcha.

I thought it was interesting, and a neat reminder about the mechanics of SQL Server.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Twitter, YouTube, and Google+

http://www.precipioservices.com