Array / Insert statement 2

[christer99]

Can I declare an array in MS SQL and then use an insert statement to loop through the array?

 

[gmmastros]

Start here faq183-5207

Let me know if this doesn't work for you

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[FlashMerlot]

We use the above technique ... a LOT. Works great.
======================
As examples, here's a function we built to convert a Comma Separated Value list of integers into a table. We use this silly little function in about 30 stored procs.
======================
ALTER FUNCTION CSVintTOTableOfIDs (@CSVs varchar(8000))
RETURNS @t TABLE(IDs int) AS
BEGIN
DECLARE @pos int, @comma varchar(1)
SET @comma=','
SET @pos=CHARINDEX(@comma,@CSVs)
WHILE @pos>0
BEGIN
INSERT @t values (cast((LEFT(@CSVs, CHARINDEX(@comma, @CSVs) - 1 )) as int))
SET @CSVs = SUBSTRING(@CSVs, CHARINDEX(@comma,@CSVs) + 1, LEN(@CSVs))
SET @pos = CHARINDEX(@comma,@CSVs)
END
IF LEN(@CSVs)>0
BEGIN
INSERT @t(IDs) SELECT @CSVs
END
RETURN
END
======================
And here's an example of how we use it.
======================
CREATE proc SpotsGridForOneClient(@ClientID int, @SpotIdsCSV varchar(300)) as
SELECT
FullName as Facility
FROM
Spots
WHERE
(Spots.ClientID = @ClientID) AND
(SpotTypes.SpotTypeID IN
(SELECT IDs from CSVintTOTableOfIDs(@SpotIdsCSV))
)
======================
Good luck

 

[gmmastros]

Yep!

Very similar to the SPLIT function shown in the faq's. The primary difference being... the version from the faq's allows you change the token that you split on. Wanna use commas, sure both do that, but if you want to use dashes (-), semi-colons (, spaces, whatever, the version in the faq's allows it.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom