Run the code in Listing 3 to create the usp_getorders
procedure, which is based on a call to the function
fn_split, as well as a join between the function and the
Orders table to return only the qualifying orders. Note
the use of a new GUID to inspect the plan caching and
reuse behavior of the new solution.
Next, run the following code to test the stored
procedure with two different inputs:
EXEC dbo.usp_getorders N'10248,10250,10249';
EXEC dbo.usp_getorders
N'10260,10270,10265,10290';
Because the query strings for both invocations are the
same, the plan produced for the first invocation of the
query can be reused for subsequent invocations. You
can confirm this by inspecting the plans associated with
the query in cache:
SELECT objtype, usecounts, sql
FROM sys.syscacheobjects -- in 2000 query master
.dbo.syscacheobjects
WHERE SQL LIKE '%89CE9E3A-943A-42F2-AEB6-
0C924CB18387%'
AND sql NOT LIKE '%sys%';
Table 2 shows the output of this query.
Note that there’s only one plan in cache, and the
usecounts attribute indicates that it was used twice.
This implementation has great performance benefits
over the dynamic SQL approach that I discussed in
“Handling Arrays” (InstantDoc ID 100453). This method optimizes the query only once,
then reuses the cached plan. Also, because there’s only
one plan in cache, the procedure creates no memory
overhead. In addition, because this implementation of
the procedure uses a static query, there’s no exposure
to SQL injection, and the solution is therefore secure.
Finally, because the fn_split function returns the position
of the element in the array (pos attribute), you can
sort the rows in the output by that position by adding
ORDER BY pos to the query.
Choices, Choices
Using a T-SQL split function to handle arrays as
inputs is a good solution—the approach is secure and
doesn’t negatively affect performance. However, the
implementation is unnecessarily complex. Because you
should try to develop solutions that are as simple and
straightforward as possible, you should consider additional
alternatives. Next month I’ll present an approach
that uses a CLR version of the split function, as well as
an implementation based on SQL Server 2008’s table
valued parameters.