A ton of single quotes in an SQL statement?

Question

I'm really struggling to make sense of an SQL query they are using at the job where I was just hired. The person who originally wrote it is no longer here so I can't ask for an explanation.

SET @Openquery=''SELECT * FROM OPENQUERY(IFOPENEDGEDB,''''''
    SET @TSQL=''SELECT  orderno, ordersuf, cono, whse, transtype, ordertype, enterdt, custno, shipto, lineno,
        qtyord, shipprod, reqprod, proddesc, unit, price, stkqtyship, prodcost, prodcat, arpvendno,
        orderaltno, netamt, icspecrecno, statustype, glcost, vendno, pdrecno, commcost, discpct,
        qtyship, taxablefl, linealtno, promofl, disccd, pricetype, qtyrel, netord, stkqtyord,
        slsrepin, slsrepout, commtype, invoicedt, returnfl, returnty, qtyreturn, transproc, canceldt,
        priceorigcd, priceoverfl, prodline, transdt, transtm, taxamount, pricecostty, operinit,
        corechgty, corecharge, botype, bono, specnstype, reqshipdt, prevqtyshp, transdttmz, xrefprodty
    FROM    PUB.oeel
    WHERE transdttmz >= '''''''''' + CONVERT(VARCHAR(50), @LastUpdatedUtc) + '''''''''''''')'';

I don't understand why the first line doesn't have a semicolon to terminate the SET command. I do not understand why multiple single quotes appear so often. What the hell does '''''''''''' mean? What could that do? Any help would be appreciated. This query is to bring information over from an OpenEdge database they are using for their ERP system. Thanks for your help.

Answer 1

Here's my step-by-step interpretation. I've added a few newlines and extra indentation for (slightly) improved readability. I also dropped the column list and used * for brevity.

The query that is ultimately executed at the innermost level is:

SELECT *
FROM PUB.oeel
WHERE transdttmz >= 'Mar  4 2022 11:59PM'

But this needs to be passed into the OPENQUERY function, so the entire query from above is put in quotes, and the embedded quotes around the date are doubled up for syntax reasons.

SELECT * FROM OPENQUERY(IFOPENEDGEDB,'
    SELECT *
    FROM PUB.oeel
    WHERE transdttmz >= ''Mar  4 2022 11:59PM''
')

Everything in the above SQL itself needs to be passed into some function not shown in your original post. For discussion purposes, I've assumed it all gets passes into an EXEC statement. This again doubles the quotes.

SET @CombinedSQL='
    SELECT * FROM OPENQUERY(IFOPENEDGEDB,''
        SELECT *
        FROM PUB.oeel
        WHERE transdttmz >= ''''Mar  4 2022 11:59PM''''
    '')
'
EXEC (@CombinedSQL)

Now for some reason, the original developer split the above up into two strings. This made things even more confusing. The first string contains the portion up through the the OPENQUERY call, including the opening quote (doubled) that wraps the innermost query. The second string contains the inner SQL, the closing quote (doubled), and the closing parentheses for the OPENQUERY call.

SET @Openquery='SELECT * FROM OPENQUERY(IFOPENEDGEDB,'''
SET @TSQL='
        SELECT *
        FROM PUB.oeel
        WHERE transdttmz >= ''''Mar  4 2022 11:59PM''''
    '')
'
SET @CombinedSQL = @Openquery + @TSQL -- Assumed
EXEC (@CombinedSQL) -- Assumed

At this point @LastUpdatedUtc is also substituted. Note the extra quotes before and after the + operators, which terminate the first part of the string and introduce the second part of the string. A simplified interpretation is SET @TSQL= 'first-part' + CONVERT() + 'second-part', where first-part end with a quote and second-part starts with a quote.

SET @LastUpdatedUtc = GETUTCDATE() -- Placeholder
SET @Openquery='SELECT * FROM OPENQUERY(IFOPENEDGEDB,'''
SET @TSQL='
        SELECT *
        FROM PUB.oeel
        WHERE transdttmz >= ''''' + CONVERT(VARCHAR(50), @LastUpdatedUtc) + '''''
    '')
'
SET @CombinedSQL = @Openquery + @TSQL -- Assumed
EXEC (@CombinedSQL) -- Assumed

Finally, everything up to this point appears to have been wrapped up into yet another containing string that presumably get executed somehow. Again, I'll just assume an EXEC.

SET @OneMoreLevelOfComplexity = '
    SET @LastUpdatedUtc = GETUTCDATE() -- Placeholder
    SET @Openquery=''SELECT * FROM OPENQUERY(IFOPENEDGEDB,''''''
    SET @TSQL=''
            SELECT *
            FROM PUB.oeel
            WHERE transdttmz >= '''''''''' + CONVERT(VARCHAR(50), @LastUpdatedUtc) + ''''''''''
        '''')
    ''
    SET @CombinedSQL = @Openquery + @TSQL -- Assumed
    EXEC (@CombinedSQL) -- Assumed
'
EXEC(@OneMoreLevelOfComplexity) -- Assumed

If you add up all the quotes (ignoring newlines), this closely matches your original post. in particular, you get 14 consecutive quotes after the date.

How can this be improved? Nested dynamic SQL execution will almost never be easy and clear. One possible approach may be to build your query in parts and use the `QUOTENAME() function to quote and nest inner SQL in outer SQL statements. The following demonstrates this:

SELECT result =
    'aaa' + 'bbb'

DECLARE @sql1 VARCHAR(1000) = '
    SELECT result =
        ''aaa'' + ''bbb''
'
PRINT '@sql1:' + @sql1
EXEC (@sql1)

DECLARE @sql2 VARCHAR(1000) = 'EXEC(' + QUOTENAME(@sql1, '''') + ')'
PRINT '@sql2:' + @sql2
EXEC (@sql2)

DECLARE @sql3 VARCHAR(1000) = 'EXEC(' + QUOTENAME(@sql2, '''') + ')'
PRINT '@sql3:' + @sql3
EXEC (@sql3)

DECLARE @sql4 VARCHAR(1000) = 'EXEC(' + QUOTENAME(@sql3, '''') + ')'
PRINT '@sql4:' + @sql4
EXEC (@sql4)

All select the same result. The print output for @sql4 looks like Print output in part

@Sql4:EXEC('EXEC(''EXEC(''''
    SELECT result =
        ''''''''aaa'''''''' + ''''''''bbb''''''''
'''')'')')

I haven't used QUOTENAME for this before, so I'm not sure if there might be some limitations that others might chime in on.