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