You can also count substrings with fn_Split(). When you pass this sentence as the delimited string and the word the as the delimiter into the sample stored procedure that Listing 3 shows, the query returns the number 9 for nine occurrences of the word the.
How to Emulate Variable-Length Parameter Arrays
In VB, you can pass variable-length parameter arrays to VB Function and Sub procedures by using the Paramarray keyword. SQL Server has no corollary to Paramarray and, therefore, no native way to accept variable-length arrays as parameters for stored procedures. Sometimes, however, variable-length parameter arrays are exactly what you need. For example, suppose a user wants to view the titles of books written by selected authors in the Pubs database. Because you don't know ahead of time how many authors the user has selected, you can't create a parameter for each author name in your stored procedure. In this situation, an object like Paramarray would be useful in a SQL Server stored procedure to capture any number of author names.
As a workaround to the SQL Server limitation, you can pass a delimited string of author names as one parameter to a single varchar variable in your stored procedure. In the stored procedure, you can use the fn_Split() function to transform the varchar variable that contains the list of author names into a table of author names. Then, you can retrieve the list of titles you want by using a standard SQL query that joins the fn_Split() table of author names with a table or view from the Pubs database. The accompanying downloadable code includes a sample Microsoft Access project file named split.adp, which includes a form for selecting author last names from a multiselect list. When you click the command button on the form, a procedure concatenates the selections and passes them as one comma-delimited string to a stored procedure through an ADO connection. The stored procedure in Listing 4 uses fn_Split() to retrieve a list of titles, author names, and prices for the grid on the form.
Increase Your Value
I don't know how I would have tackled my telemarketing data problem without table-valued functions. I know the project would have taken a lot longer and would have been a lot messier without the fn_Split() UDF. The more UDFs I create, the more applications I find for them in my work. With UDFs in my development arsenal, I work faster and better. On my list of best new features in SQL Server 2000, user-defined functions command the top spot.