1

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 + '%' ";
Salman A
  • 262,204
  • 82
  • 430
  • 521
GuidoG
  • 11,359
  • 6
  • 44
  • 79

1 Answers1

7

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 + '%'
Salman A
  • 262,204
  • 82
  • 430
  • 521
  • 2
    To elaborate a bit. `t.ChassisNumber like ''%@ChassisNumber%'' ` was effectively looking for the **literal** string `'@ChassisNumber'` in the column `t.ChassisNumber`, not the value of the parameter `@ChassisNumber`. – Thom A Nov 30 '18 at 12:39
  • The sql statement was generated by an `SqlCommand` I had to modify that code so it generated the correct statement. I put that info as an edit to my answer – GuidoG Nov 30 '18 at 13:12