SQL Server Magazine

Hardly a week goes by in the newsgroups that a SQL Server developer doesn't ask how to pass a variable-length parameter array to a stored procedure--and learns that the technique isn't possible. You can use variable-length parameter arrays in Visual Basic (VB), but what if you prefer to program in T-SQL? Last year, I took advantage of SQL Server 2000's new support for user-defined functions (UDFs) to develop a handy UDF that emulates VB's Split() function. Later, I realized I could use the UDF to create a workaround that would let developers incorporate variable-length parameter arrays into their T-SQL programming. Let's look at why I needed the UDF, how I programmed it to solve my business problem, and how I applied it to emulate variable-length parameter arrays.

In "User-Defined Functions," November 2000, author Robert Vieira invited readers to email him new ways to apply UDFs. Shortly after reading Vieira's article, I began a project that involved parsing thousands of lines of ASCII text for a telemarketing firm. The rows of text represented INSERT queries, which were written in a proprietary language instead of standard SQL. My job was to transform these queries into SQL and run them to complete a complex data transformation. Some queries were as long as 1400 characters. Thinking aloud about how to attack my query problem, I said to a coworker, "I wish SQL Server had a function like the Visual Basic Split() function to help me parse these queries." My coworker replied, "Why don't you make your own SQL Server split function?" So, I created the fn_Split() multistatement table-valued function. (A multistatement table-valued function is essentially a stored procedure that returns a table that you can embed in a query.)

Before I describe fn_Split(), however, let's examine the Split() function. In VB, the Split() function fills a string array with values that it parses from a delimited string. Split() accepts the delimited string as its first parameter and the delimiter as its second parameter. In Split(), the default delimiter is a space. The array index is zero-based, so the first print statement in the following code prints the word brown to the Immediate window:

Dim astr() as String
astr = Split("How now brown cow")
Debug.print astr(2)  'Prints "brown" using space delimiter
astr = Split("How now brown cow", "")
Debug.print astr(2)   'Prints "w" using zero-length string delimiter

Note that the code uses a space as the default delimiter. If the delimiter is a zero-length (empty) string, the function returns an array of single characters and spaces. For example, the second Split() function statement in the above code uses an empty string as its delimiter, so the second print statement prints the character w. The array for the second split has 17 elements, one for each character or space in the string.

Building the Fn_Split() Function
Listing 1 shows the fn_Split() function. Because the Split() function has been so useful in my VB development work, I decided to make fn_Split() resemble it as much as possible. However, SQL Server can't pass an array of values between procedures. So instead of returning an array of values, as Split() does, fn_Split() returns a two-column SQL Server table variable.

   Prev. page   [1] 2 3 4     next page