Store sub-results (rows) during a query and filter them by input parameters

Roland

17y3.3k

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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Roland17y ago
I don't know if it is a good idea if you have thousands of records to retrieve them all and filter them in a dataview. Doesn't it have an impact on performance? I think using dynamic sql would be still faster. I'm not sure though. I found out in the meantime that I should use table variables which I can use as a normal table, but it won't exist after the execution of the query. However I have to make more research on that. Any other suggestions on the question?
Gautam Baruah17y ago
You can go with dynamic stored procedure but in that case it wont be a pre-complied version so, as you said it will be slower. Better, in your code behind file use dataset and then use dataview e.g.

SqlDataAdapter adp=new SqlDataAdapter();
dataset ds=new dataset();
ds.Fill(ds,"table1");

dataview dv=ds.Tables["table1"];
dv.FilterExpression="minPrice > 100 and maxPrice < 200";
// you can use any filter expression.

Now you can bind this dataview with any control like gridview.

Direct filter expression in sql query always gives slower performance as its a direct connectivity with database. So, better go with dataview which is a disconected form.

Please let me know if I am going correct with your question.