Store sub-results (rows) during a query and filter them by input parameters
I need to write a filter function for my application where I have to filter by
- a searchstring
- min. price
- max. price
- category
for now. I have read a bit about dynamic sql and that it's slow to execute and unsafe. I have an idea for my task, but I'm not sure if it's possible.
I would like to create a stored procedure which runs a default query.
select * from Items
There will be 4 input parameters
@exp nvarchar(max),
@minprice decimal,
@maxprice decimal,
@catid int
After the default query I need to store the result somehow and run an other query depending on the value of @exp lets say.
if @exp NOT NULL
select * from earlierResults(?) where itemname like '%'+@exp'%'
Basically I need to replace the earlierResults with something. Plus I'm not sure how the column names will work from these results.
And I need to further filter it down with the other parameters.