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.
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:
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.