SQL declared variable as nvarchar. Use dec variable as 'like' or 'Contains' in where clause

Question

I have declared my variable:

declare @search as nvarchar
set @search = 'road'

Then I effectively want to have this in my Where clause:

where  

unit.BuildingName like '%@search%' or
property.Street like '%@search%' or
unit.Street like '%@search%'

Obviously not going to work as this isn't even looking at my declared variable.

But can you see what I am trying to get at?

Thanks!

Answer 1

You should change the query like this.

declare @search as nvarchar(500)
set @search = '%road%'

where  
unit.BuildingName like @search or
property.Street like @search or
unit.Street like @search