Like search in execute_sql not working properly

Question

I'm facing a very strange behavior of a like search within a sp_executesql:

This statement returns 0 rows:

exec sp_executesql N'SELECT * FROM MyTable WHERE Name LIKE ''%'' +
     @Name + ''%''',N'@Name nvarchar(7)',@Name=N'100024​'

When this equivalent returns the desired row:

DECLARE @Name nvarchar(7)=N'100024'    
SELECT * FROM MyTable WHERE Name LIKE '%' + @Name + '%'

What's wrong with exec sp_executesql command? if I use it to search other row such as "100033" it finds the row, so syntax must be fine (in fact the query was taken from SQL Profiler when trying to debug why my asp.net page didn't found this particular element)

Answer 1

You have an invisible symbol here @Name=N'100024​'

Answer 2

You have strange unicode characters in the N'100024​' part. Try delete the part N'100024​' and write it anew.

Example - first line works ok, second line does not work:

exec sp_executesql N'SELECT * FROM #MyTable WHERE Name LIKE ''%''+@Name+''%''', N'@Name nvarchar(200)',@Name=N'100024'
exec sp_executesql N'SELECT * FROM #MyTable WHERE Name LIKE ''%''+@Name+''%''', N'@Name nvarchar(200)',@Name=N'100024​'

Try to save the query in SQL management studio and then open it in you will see characters like: @Name=N'100024‚Äč'. It will prompt you if you want to save in unicode.

Answer 3

You seem to be have an incorrect number of single quotes ('). This (copied from your sp_execute bit):

DECLARE @Name nvarchar(7);
SET @Name = '100024​'
PRINT 'SELECT * FROM MyTable WHERE Name LIKE ''%'' +      @Name + ''%'''

outputs this:

SELECT * FROM MyTable WHERE Name LIKE '%' +      @Name + '%'

Try removing a single quote prior and after the @name:

'SELECT * FROM MyTable WHERE Name LIKE ''%' +      @Name + '%'''

Output:

SELECT * FROM MyTable WHERE Name LIKE '%100024?%'