John Mendez

I have run across some situations when programming where I wish I could pass an “array” into a SQL stored procedure.  What I mean by “array” is a string that is delimited by a certain character (i.e., string1;string2;string3).  The reason as to why I like having this ability is because when I’m programming I do not want to have a loop inside one of my data classes executing multiple SQL statements.  I would rather have the multiple SQL statements be taken care of on the stored procedure side of things. Lucky for me one of our authors, Q , came up with SQL code that will iterate a string.

Let me provide an example of the situation you could use this in.

tbl_Requests -> holds basic information about a request.  The column names are below

 intRequestID
 strRequestName
 strRequestor
 dtRequested
 blAdditionalInfo

The blAdditionalInfo column is a flag saying there are an unknown amount of miscellaneous details that go along with this request that are not standard.

tbl_RequestsMiscItems - > holds miscellaneous items / details

 intMiscItemID
 intRequestID
 strItemName

Now that we have two simple tables to help out, lets say I am filling out a request and fill in the basic information, however, as a programmer you don’t know how many misc. items there may be related to the request…hence the separate table for it.  Instead of my code handling making separate calls to SQL or executing multiple statements inside my code to insert the additional items, I can just store all of the misc. items into a string delimited by a character.  For example when they submit the form my values may look like this:

 strRequestName = “Request 1”;
 dtRequested = DateTime.Now;
 blAdditionalInfo = true;
 strAdditionalInfo = “item1;item2;item3;item4;item5;item6;”;

Now we handle it on the SQL side

Below is the SQL code that will allow you to iterate through the delimited string:

DECLARE @strItem NVARCHAR(255)
DECLARE @aryIndex INT

DECLARE @aryItems NVARCHAR(2000)
SET @aryItems = N' item1;item2;item3;item4;item5;item6;'

SET @aryIndex = CHARINDEX(N';', @aryItems)
WHILE(@aryIndex>0)
BEGIN
SET @strItem = SUBSTRING(@aryItems, 1, @aryIndex-1)

SELECT @strItem AS [Item Name]
,@aryItems AS [Collection]
,@aryIndex AS [Current Index]

SET @aryItems = SUBSTRING(@aryItems, @aryIndex+1, LEN(@aryItems)-@aryIndex)

SET @aryIndex = CHARINDEX(N';', @aryItems)
END

Now lets use the code concept above to our advantage and use it to insert values in tbl_RequestsMiscItems (table name above)

DECLARE @strItem NVARCHAR(255)
DECLARE @aryIndex INT

DECLARE @aryItems NVARCHAR(2000)
SET @aryItems = N' item1;item2;item3;item4;item5;item6;'

SET @aryIndex = CHARINDEX(N';', @aryItems)
WHILE(@aryIndex>0)
BEGIN
SET @strItem = SUBSTRING(@aryItems, 1, @aryIndex-1)

INSERT INTO tbl_RequestsMiscItems
(intRequestID, strItemName)
VALUES(@intRequestID, @strItem)

SET @aryItems = SUBSTRING(@aryItems, @aryIndex+1, LEN(@aryItems)-@aryIndex)

SET @aryIndex = CHARINDEX(N';', @aryItems)
END

For the purpose of the example I assigned @aryItems statically, however, in a real stored procedure, that would be a parameter of the string assigned in the code.

Microsoft SQL sql