SQL Server Performance Forum – Threads Archive

Help: Arrays in StorProc Parms?

Hello, I’m fairly new to SQL Server and could use some help. I have a stored procedure that selects values using an ‘IN’ condition in the Where clause. The entries in the IN condition can vary and are passed in on a parm to the stored procedure. What I have so far is something like this: alter procedure xxxxx
@grp1 int = null,
@grp2 int = null,
@grp3 int = null select <blah blah blah>
where group in (@grp1,@grp2,@grp3) This works OK. I can pass one, two or three parameters to this and by specifying the null defaults, I don’t have to pass more than I need. (I actually have more parameters, but I simplified this example.) The only problem with this is I don’t really know what the maximim number of values that might need to be passed is. I could take a guess that maybe 5 or 10 parameters is enough, but there’s no certainty. Is there some way I can pass a variable number of parms to this? An array of parms would be great. Or is there a way to simply define a literal with the values? I tried that and was unable to get the IN condition to work with it, but maybe there’s a way. It seems like passing an array should be doable and is probably the right approach, but I don’t know if it can be done or how to do it. Can anyone help me out? Thanks.

---

You can use split function to parse the array or pass the input as xml string. So that you can pass unlimited number of parameters. Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

Try this function for split array. CREATE FUNCTION [dbo].[usp_ParseArray]
(
@StringVARCHAR(8000),
@DelimeterCHAR(1) = ‘,’
)
RETURNS @Array TABLE
(
[index]INTEGERIDENTITY(0,1) NOT NULL,
itemVARCHAR(8000)
) WITH SCHEMABINDING AS BEGIN DECLARE @itemVARCHAR(8000),
@PosINTEGER,
@NextPosINTEGER,
@LenInputINTEGER,
@LenNextINTEGER,
@DelimLenINTEGER SET @Pos = 1
[email protected] = LEN(@Delimeter)
[email protected] = LEN(@String)
[email protected] = CHARINDEX(@Delimeter, @String, 1) –Does not work for space
IF (@Delimeter = ‘ ‘)
RETURN –begin looping process
WHILE (@Pos <= @LenInput) AND (@NextPos > 0)
BEGIN IF (@NextPos > @Pos)
BEGIN SET @LenNext = @NextPos – @Pos
SET @Item = LTRIM(RTRIM(SUBSTRING(@String, @Pos, @LenNext))) IF (LEN(@Item) > 0)
BEGIN INSERT INTO @Array (item) VALUES (@item) END
END SET @Pos = @NextPos + @DelimLen
[email protected] = CHARINDEX(@Delimeter, @String, @Pos)
END –see if there is a residual item
SET @item = LTRIM(RTRIM(SUBSTRING(@String, @Pos, @LenInput – @Pos + 1))) IF (LEN(@item) > 0)
BEGIN
INSERT INTO @Array (item) VALUES (@item)
END RETURN END
Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

OR
Here is the simple example using xml string. CREATE PROCEDURE usp_InsertMultipleCustIDs
@xmlInfo varchar(4000) = NULL AS
SET ANSI_WARNINGS ON
BEGIN
–|local variable declaration
DECLARE @xmlHandlerint /* Create an internal representation of the XML document */
EXEC sp_xml_preparedocument @xmlHandler output, @xmlInfo /*** Parse XML and extract the info into variables ***/
DECLARE @tmpCustID TABLE (CustID int)
[email protected] (CustID)
SELECTCustID
FROMOPENXML(@xmlHandler, ‘/customerinfo/customer’, 2)
WITH(CustIDvarchar(32)’@custid’)
/* Remove an internal representation of the XML document */
EXEC sp_xml_removedocument @xmlHandler
— Display the results
SELECT*
FROM @tmpCustID END
GO — EXEC dbo.usp_InsertMultipleCustIDs
— ‘<customerinfo>
— <customer custid="15122"> </customer>
— <customer custid="97851"> </customer>
— <customer custid="15911"> </customer>
— </customerinfo>’
Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

Thank you for the help.

---

Check the following url for more info.. Arrays and Lists in SQL Server 2005
http://www.sommarskog.se/arrays-in-sql-2005.html
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

]]>