New training course: Customizing SQL Prompt. Watch now.

Ctrl + K + Y adding extra characters

This occurred when I was helping to tune a query (I don't need help with the tuning). It includes a query against a varchar column with the % wildcard before and after the search filter used with LIKE. Here is the query:

exec sp_executesql N'WITH __FP_RESULTS AS (SELECT [TABLE1].*, [TABLE2].*, ROW_NUMBER() OVER (ORDER BY [TABLE1].[__PRIMARY] ASC) __ROW_NUM FROM [TABLE1] INNER JOIN [TABLE3] ON [TABLE3].[SKPI2_ID] = [TABLE1].[__PRIMARY] LEFT JOIN [TABLE2] ON [TABLE2].[__RECORDTYPE] = @RecordType AND [TABLE2].[__RECORDLINK] = [TABLE1].[__PRIMARY]  WHERE (([TABLE1].[Customer Code] LIKE ''%'' + @_FILTER_01 + ''%'' )) AND [TABLE2].[__STATUSCODE] IN (1,4) AND [TABLE2].[__STAGEID] > 0) SELECT * FROM __FP_RESULTS WHERE (__ROW_NUM BETWEEN @START__ AND @END__) ORDER BY __ROW_NUM ASC',N'@_FILTER_01 nvarchar(6),@RecordType nvarchar(14),@START__ int,@END__ int',@_FILTER_01=N'STR018',@RecordType=N'CF_SALES_KPI_2',@START__=1,@END__=100

I copied it in SSMS as one long string as above and used Ctrl + K + Y to format it. When formatting the code SQLPrompt added extra spaces either side of each % in the LIKE predicate. When I ran the formatted query I was confused as I got no results as the query had changed the search string. It took me a while to spot the issue and when tested the formatting discovered it was SQLPrompt.

Here is the result of the formatting completed by SQLPrompt:

EXEC sp_executesql N'WITH __FP_RESULTS
AS (SELECT [TABLE1].*,
           [TABLE2].*,
           ROW_NUMBER() OVER (ORDER BY [TABLE1].[__PRIMARY] ASC) __ROW_NUM
    FROM [TABLE1]
        INNER JOIN [TABLE3]
            ON [TABLE3].[SKPI2_ID] = [TABLE1].[__PRIMARY]
        LEFT JOIN [TABLE2]
            ON [TABLE2].[__RECORDTYPE] = @RecordType
               AND [TABLE2].[__RECORDLINK] = [TABLE1].[__PRIMARY]
    WHERE (([TABLE1].[Customer Code] LIKE '' % '' + @_FILTER_01 + '' % ''))
          AND [TABLE2].[__STATUSCODE] IN ( 1, 4 )
          AND [TABLE2].[__STAGEID] > 0)
SELECT *
FROM __FP_RESULTS
WHERE (__ROW_NUM
      BETWEEN @START__ AND @END__
      )
ORDER BY __ROW_NUM ASC',
                   N'@_FILTER_01 nvarchar(6),@RecordType nvarchar(14),@START__ int,@END__ int',
                   @_FILTER_01 = N'STR018',
                   @RecordType = N'CF_SALES_KPI_2',
                   @START__ = 1,
                   @END__ = 100;
I should point out that I formatted the whole statement using Ctrl + K + Y and then highlighted the query starting with WITH and ending with ASC and formatted the SQL using Ctrl + K + Y in unison. It was this second formatting event that caused the error. I am wondering if it is to do with the double quotes to represent single quotes in the query passed to sp_executesql.

Not really a question just wanted to let you know it was happening.
Tagged:

Answers

Sign In or Register to comment.