CHAPTER 6 BUILDING REPORTS
Figure 6-31. Multiple parameter selectionYou also have to decide the best way to parse the string as it is returned from the report, both for
performance and versatility. You have two effective methods for doing this, either dynamic SQL or a
UDF. Creating dynamic SQL, which is essentially building a variable SQL expression using variables
defined by user input, is cumbersome and syntactically challenging. Wrapping SQL statements within
quotes and programmatically concatenating variables is time-consuming and often frustrating, and
yields unpredictable results. What is worse is that it opens itself up to SQL injection hacks where users
can interject values as strings that may execute statements that the developer did not intend. The best
way to handle string values for MVPs is through a UDF that parses the individual values and feeds these
into the IN clause of the query. Knowing that the values will always be returned in a comma-separated
string makes loading the values into an accessible table much easier, by using a function designed for
this purpose. This type of function is called a table-valued function, because the parsed rows of the input
string are loaded into a table that can then be referenced as a subquery in the calling stored procedure.