StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POT-SQL procedure - filter parameter as Object/CLR/Xml/UDT
text
Body
copied!<p><strong>Cliffs: Is there a known pattern for passing a standard "filter" type to a stored procedure to encapsulate stardate/enddate/pagesize/pagenum parameters?</strong></p> <p>Not sure the correct place for this question. I'm exploring the idea of passing a filtering object parameter to a stored procedure which encapsulates our common filtering parameters (startdate, enddate, pagenumber, pagesize, list of int's, etc). The reason for this is to reduce the amount of similar parameters and boilerplate SQL spread around our procedures. This would give us a more standard interface and starting point for each procedure right from the start. I haven't been able to find much info on the topic.</p> <p>Pattern I've noticed - when first building most SP's they start with a single id parameter used in the where clause. At some point later, you may need to add parameters for date range parameters (startdate, enddate or dynamic ranges "ytd, mtd, dtd"). If the data set is large enough you also may need to introduce pagesize/pagenum for server side paging. After some time you may realize that you need results for a list of id's rather than a single id, so you add a CSV or XML parameter to envelope the IDs.</p> <p>Ultimately many stored procedures end up with a lot of similar boilerplate and (hopefully) identical parameters for handling these standard filtering parameters. I'm trying to research known patterns for passing an encapsulated filter object parameter to my procedures, that ideally would be strongly typed on the C# side. This would be particularly useful when managing a group of procedures that power reports which all require the same filtering options (in addition to the report-specific query parameters).</p> <p>My goal is to reduce the number of parameters required to the bare minimum needed for the WHERE clause, and create a standard mechanism for passing the generic filtering options into a procedure and using those values while inside a procedure. How could this be achieved through XML or CLR or UDT parameters? </p> <p>For context of this question, I'm using SQL Server 2008 via ADO.Net from C# 2.0. Unfortunately LINQ/EF is not an option for this project at this point, and we must stick with our existing RDBMS. If there is a known pattern that requires changing technologies I would be interested in hearing about it. </p> <p><strong>Edit</strong>: Appreciate the replies so far. I've added a bounty for 50pts that I'll let run for a few more days to try to promote some more discussion. If my question isn't clear enough just leave a comment.. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload