Monday, March 12, 2007

Nested Stored Procedures & Data results

For my project at work, I went about building the search capabilities today. The system has two main data types that can be searched for mutually exclusive of one another, and later iterations of the software may have other data types which can be searched for then. My solution was a Windows.Form which would display the search results and allow the user to enter a search term list (separated by a comma) and research. The search itself would be powered by a stored procedure hiding in SQL Server.

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

No comments:

Blog Widget by LinkWithin