2

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
GSussman
  • 31
  • 4
  • 1
    `PRINT` your dynamic SQL and debug that first, then move the solution to the dynamic statement. – Thom A Nov 16 '20 at 23:27
  • 2
    Also: [Bad Habits to Kick : Declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length) – Thom A Nov 16 '20 at 23:28
  • 3
    Also `'['+@TABLENAME+']'` is *not* safe from injection. Use `QUOTENAME` to safely inject object names. – Thom A Nov 16 '20 at 23:29
  • @Larnu that was one of the first things I did during debugging. Print and copy of the dynamic SQL succeeds. – GSussman Nov 16 '20 at 23:29
  • 1
    Then so too would sending it to `sys.sp_executesql`. Take the time to make us a [mre] here. – Thom A Nov 16 '20 at 23:30

1 Answers1

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)
*/
seanb
  • 6,272
  • 2
  • 4
  • 22