Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ' ' with sp_executesql

Question

I am dynamically building a SQL statement based on operations from a couple of different tables. The salient part of the SQL is below.

DECLARE @SQL NVARCHAR(MAX) = NULL
...
SELECT @sql = 'TRIM(CAST(' + STRING_AGG(EXPORT_COL, ' AS VARCHAR)) + '','' + TRIM(CAST(') FROM #TEMP_TABLE
SET @sql = 'SELECT''(''+'+@sql+' AS VARCHAR))+'')'''+'FROM '+'[mydatabase].[dbo].['+@TABLENAME+']'
SET @sql = REPLACE(@sql,'''','''''')

When I call the code using sp_executesql

EXEC sp_executesql @sql

I get this error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''

If I query @sql or print the value to the messages window in SSMS I get:

SELECT''(''+TRIM(CAST(COL1 AS VARCHAR)) + '','' + TRIM(CAST(COL2 AS VARCHAR))+'')''FROM [mydatabase].[dbo].[DATA_TABLE] 

which is the output I would expect.

Copying the text and calling sp_executesql using a quoted version of the output string, the query succeeds with no error.

EXEC sp_executesql N'SELECT''(''+TRIM(CAST(COL1 AS VARCHAR)) + '','' + TRIM(CAST(COL2 AS VARCHAR))+'')''FROM [mydatabase].[dbo].[DATA_TABLE]'

I have already checked for the presence of non-printable characters as indicated in this post

I have also implemented a function that "should" strip out any non printable characters per this post. Yet the problem persists.

SQL Server 2017 Express (v14.0.1000.169) on Windows Server 2019 standard.

Answer 1

You need to be really careful about when and what parts need single quotes vs which parts need doubled quotes.

If you are writing the string to assign it to a variable, it needs the doubled quotes. If, however, the string already has the quote inside, it doesn't need to be doubled again.

Here's a simplified example showing the issues/approach

CREATE TABLE #Test (TestVal varchar(100));
INSERT INTO #Test (TestVal) VALUES ('abcde');

Now, when running the process with doubled quotes (similar to yours), here are the results

DECLARE @SQL2 nvarchar(max) = 'SELECT ''''('''' + TestVal + '''')'''' FROM #Test;'
PRINT @SQL2;
/* -- Result
SELECT ''('' + TestVal + '')'' FROM #Test;
*/

EXEC sp_executesql @SQL2;
/* -- Result
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ''.
*/

EXEC sp_executesql N'SELECT ''('' + TestVal + '')'' FROM #Test;';
/* -- Result
(abcde)
*/

Note that in the bottom command, the doubled quotes were needed so that the string would contain single quotes - and therefore works. However, when already in the string, it made the command fail.

Now, if we make the variable just have single quotes, it works

DECLARE @SQL3 nvarchar(max) = 'SELECT ''('' + TestVal + '')'' FROM #Test;'
PRINT @SQL3;
/* -- Result
SELECT '(' + TestVal + ')' FROM #Test;
*/

EXEC sp_executesql @SQL3;
/* -- Result
(abcde)
*/