[SOLVED] How to use `sp_executesql` with a ´like` and a ´parameter`?

How to use `sp_executesql` with a ´like` and a ´parameter`?

I cant get an sp_executesql working with a very simple statement.

When I do this

select * from vwChassisHistory t where t.ChassisNumber like '%1234%'

it retuns 214 rows, as I expect

But when I do this

exec sp_executesql 
  N'select * from vwChassisHistory t where t.ChassisNumber like ''%@ChassisNumber%'' ',
  N'@ChassisNumber varchar(4)',@ChassisNumber='1234'

it returns 0 rows

So I must be making a very stupid mistake here, but I just cannot see it.
How to use sp_executesql with a like and a parameter ?

I lookat at this but this question is about dynamicly building the statement so it does not help me
Also this does not has an answer for me

EDIT
To get the SqlCommand to generate the sql as in the accepted answer, I had to modify the code from this

string chassisNumber = "1234";

string sql = "select * from vwChassisHistory t ";
string where += "where t.ChassisNumber like '%@ChassisNumber%' ";

to this

string where = "where t.ChassisNumber like '%' + @ChassisNumber + '%' ";

Solution

You need to separate the @parameter and the % characters:

exec sp_executesql 
  N'SELECT * FROM vwChassisHistory t WHERE (t.ChassisNumber LIKE ''%'' + @ChassisNumber + ''%'')',
  --                                                                   ^                ^
  -- ------------------------------------------------------------------+                |
  -- -----------------------------------------------------------------------------------+
  N'@ChassisNumber VARCHAR(4)', @ChassisNumber = '1234'

In your original query the parameter name becomes part of a string literal and SQL Server ends up searching for the substring @ChassisNumber:

SELECT * FROM vwChassisHistory t WHERE t.ChassisNumber LIKE '%@ChassisNumber%'

The revised query is executed as follows:

SELECT * FROM vwChassisHistory t WHERE t.ChassisNumber LIKE '%' + @ChassisNumber + '%'