Advice on Minimizing Stored Procedure Parameters
- by RPM1984
Hi Guys,
I have an ASP.NET MVC Web Application that interacts with a SQL Server 2008 database via Entity Framework 4.0.
On a particular page, i call a stored procedure in order to pull back some results based on selections on the UI.
Now, the UI has around 20 different input selections, ranging from a textbox, dropdown list, checkboxes, etc.
Each of those inputs are "grouped" into logical sections.
Example:
Search box : "Foo"
Checkbox A1: ticked, Checkbox A2: unticked
Dropdown A: option 3 selected
Checkbox B1: ticked, Checkbox B2: ticked, Checkbox B3: unticked
So i need to call the SPROC like this:
exec SearchPage_FindResults @SearchQuery = 'Foo', @IncludeA1 = 1, @IncludeA2 = 0, @DropDownSelection = 3, @IncludeB1 = 1, @IncludeB2 = 1, @IncludeB3 = 0
The UI is not too important to this question - just wanted to give some perspective.
Essentially, i'm pulling back results for a search query, filtering these results based on a bunch of (optional) selections a user can filter on.
Now, My questions/queries:
What's the best way to pass these parameters to the stored procedure?
Are there any tricks/new ways (e.g SQL Server 2008) to do this? Special "table" parameters/arrays - can we pass through User-Defined-Types? Keep in mind im using Entity Framework 4.0 - but could always use classic ADO.NET for this if required.
What about XML? What are the serialization/de-serialization costs here? Is it worth it?
How about a parameter for each logical section? Comma-seperated perhaps? Just thinking out loud.
This page is particulary important from a user point of view, and needs to perform really well. The stored procedure is already heavy in logic, so i want to minimize the performance implications - so keep that in mind.
With that said - what is the best approach here?
