Ctrl + K + Y adding extra characters
dickiebow
Posts: 1 New member
in SQL Prompt
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:
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:
Not really a question just wanted to let you know it was happening.
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
You're correct in that the behavior is due to the double single quotes used to escape the strings in the dynamic SQL. Unfortunately we don't currently support formatting of dynamic SQL, but you should vote on this Uservoice suggestion for that functionality: https://redgate.uservoice.com/forums/94413-sql-prompt/suggestions/19374085-format-dynamic-sql
Kind regards,
Alex
Have you visited our Help Center?