No big deal right?
Well it worked until I tried to parse the search terms list out. I knew I would have multiple stored procedures doing the heavy work of searching, so I only wanted to keep maintain the functionality to split the search term out into an array in a single place. So I stuck it into a stored procedure.
Smart huh?
One would think so. Unfortunately, SQL Server does not allow for the manipulation of data sets returned from Stored Procedures - and this includes that they do not allow me to stick that returned data set into a temporary table so I can walk it with a cursor to perform my searches.
Annoying to say the least.
So I turned to Google for a bit, and then realized that some of my co-workers had a similar problem in their project. So I came out of my den... err, my office and wandered across the hall to talk to the guy over there.
He showed me the solution they're using, and I felt like smacking myself on the forehead.
It was that little used option for manipulating data in SQL Server in composite chunks. You know, FUNCTIONS.
Apparently, the table dataset that a SQL Server function which returns can be manipulated by a calling Stored Procedure or function.
Since, I got the base Stored Procedure for splitting a search string into tabular format from the web, I figured I would post how I changed that to a function on the web.
/********************************************************************************
* Procedure Name: ParseArray *
* Author: graz@sqlteam.com *
* Purpose: Takes two inputs, a csv style string *
* AND the seperator. Returns a table of all the values *
* in the CSV style string *
* *
* History: *
* 03/07/2007 Modified to be a Function
* *
********************************************************************************/
CREATE FUNCTION dbo.ParseArray
(
@Array VARCHAR(1000),
@Separator CHAR(1)
)
RETURNS @Return TABLE (Word VARCHAR(1000) )
AS
BEGIN -- FUNCTION
-- @Array is the array we wish to parse
-- @Separator is the separator character such as a comma
DECLARE @Separator_Position INT; -- This is used to locate each separator character
DECLARE @Array_Value VARCHAR(1000); -- this holds each array value as it is returned
DECLARE @Temp INT;
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
SET @Array = @Array + @Separator
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @Separator + '%' , @Array) <> 0
BEGIN -- While PATINEDEX('%' + @Separator + '%' , @Array) <> 0
-- patindex matches the a pattern against a string
SELECT @Separator_Position = PATINDEX('%' + @Separator + '%' , @Array)
SELECT @Array_Value = LEFT(@Array, @Separator_Position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
-- insert @array_value into the temp. table
IF LTRIM(RTRIM(@Array_Value)) = '' OR @Array_Value = NULL
BEGIN -- LTRIM(RTRIM(@Array_Value)) = '' OR @Array_Value = NULL
SET @Temp = 1;
END -- LTRIM(RTRIM(@Array_Value)) = '' OR @Array_Value = NULL
ELSE -- LTRIM(RTRIM(@Array_Value)) = '' OR @Array_Value = NULL
BEGIN -- ELSE: LTRIM(RTRIM(@Array_Value)) = '' OR @Array_Value = NULL
INSERT INTO @Return (Word) VALUES (LTRIM(RTRIM(@Array_Value)));
END -- ELSE: LTRIM(RTRIM(@Array_Value)) = '' OR @Array_Value = NULL
-- This replaces what we just processed with and empty string
SELECT @Array = STUFF(@Array, 1, @Separator_Position, '');
END -- While PATINEDEX('%' + @Separator + '%' , @Array) <> 0
-- return all rows from temp table
RETURN;
END -- FUNCTION