I want to search in SQL Server with must have parameter in one colunm

Question

I am using c# and SQL Server 2008.

I have table like this

id | propertyTypeId | FinishingQualityId  |    title   | Description |  features  
1            1             2                 prop1          propDEsc1    1,3,5,7
2            2             3                 prop2           propDEsc2    1,3      
3            6             5                 prop3           propDEsc3    1
4            5             4                 prop4           propDEsc4    3,5
5            4             6                 prop5           propDEsc5    5,7
6            4             6                 prop6           propDEsc6

and here is my stored code (search in the same table)

create stored procdures propertySearch
as
@Id int = null,
@pageSize float ,
@pageIndex int,
@totalpageCount int output,
@title nvarchar(150) =null ,
@propertyTypeid int = null ,
@finishingqualityid int = null ,
@features nvarchar(max) = null , -- this parameter send like 1,3 ( for example)

begin

select 
    row_number () as TempId over( order by id) , 
    id, title, description, 
    propertyTypeId, propertyType.name, 
    finishingQualityId, finishingQuality.Name,
    freatures
into #TempTable from property 
join propertyType on propertyType.id= property.propertyTypeId
join finishingQuality on finishingQuality.id = property.finishingQualityId
where 
property.id = isnull(@id,property.id ) and proprty.PropertyTypeId= isnull(@propertyTypeid,property.propertyTypeId)

select totalpageconunt = ((select count(*) from #TempTable )/@pageSize )

select * from #TempTable  where tempid between (@pageindex-1)*@pagesize +1 and (@pageindex*@pageSize)
end
go

I can't here filter the table by feature I sent. This table has too many rows I want to add to this stored code to filter data for example when I send 1,3 in features parameter I want to return row number one and two in the example table I write in this post (want to get the data from table must have the feature I send)

Many thanks for every one helped me and will help

score 0 · Answer 1 · edited May 23 '17 at 12:30

Sending a delimited list of values to match with a delimited value in a column will generally get you into trouble as the only way to do it is to split each value out from the string. (Also why indexing them is pointless)

You should create an additional table containing the id from your property table & a row for each feature;

property_id feature 
----------- -------
5           5
5           7

Then matching is much simpler. You can send the procedure the list of features you want to match ideally using a table valued parameter or alternatively a table function.

I want to search in SQL Server with must have parameter in one colunm

1 Answers1