Sunday, July 19, 2009

How to create a multivalue array in SQL

I had to figure this out when I was creating reports. The report had to be based on different document types depending on the choices of the individual. So lets say theres 5 different document types. Well a simple query to check all 5 document types would be

SELECT * FROM Table WHERE DocType IN (1, 2, 3, 4, 5). Well now you have a very complicated query to deal with when you have to exclude document types. The simplest way is to save the "IN" string as a multivalue array. I.e. you would use the string "1, 2, 3, 4, 5".

Well here's how you do this:

  

1. First create a function that's used to parse the parameters can turn it into a
multivalued array
IF EXISTS(SELECT * FROM sysobjects WHERE ID = OBJECT_ID(’UF_CSVToTable’))
DROP FUNCTION UF_CSVToTable
GO

CREATE FUNCTION UF_CSVToTable
(
@psCSString VARCHAR(8000)
)
RETURNS @otTemp TABLE(sID VARCHAR(20))
AS
BEGIN
DECLARE @sTemp VARCHAR(10)

WHILE LEN(@psCSString) > 0
BEGIN
SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),
LEN(@psCSString)))
SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),
LEN(@psCSString)) + 1, LEN(@psCSString))
INSERT INTO @otTemp VALUES (@sTemp)
END

RETURN
END
Go

2. Create the stored procedure that will use the function from step 1.
CREATE
PROCEDURE TEMP_SP_RETRIVE_JOBS
@sCategoryID VARCHAR(5000)
AS
BEGIN
SELECT * FROM TblJobs WHERE iCategoryID IN (SELECT * FROM UF_CSVToTable(@sCategoryID))
END
GO

No comments:

Post a Comment

There was an error in this gadget