SQL Server, Dynamic Array of Array loading question

[DigitalBuilder]

SQL Server, Dynamic Array of Array loading question


Quote:

--
--Question
--
-- How to load in array from @value
-- Or is there another way?
--

--@12 will be an array type, array of array
Declare @12 varchar(200)
Set @12='(1,2,3)'

if (@12<>null)
BEGIN
Select 12 from FlatArrayMetaType
inner join FlatArrMetaTypeDataUserData on FlatArrMetaTypeDataUserData.FlatArrayMetaTypeID=Fl atArrayMetaType.FlatArrayMetaTypeID
inner join Flat on FlatArrMetaTypeDataUserData.flatid =flat.flatid
--
-- (1,2,3) works
--
where FlatArrMetaTypeDataUserData in (1,2,3)
END


--@12 will be an array type, array of array
Declare @12 varchar(200)
Set @12='(1,2,3)'
if (@12<>null)
BEGIN
Select 12 from FlatArrayMetaType
inner join FlatArrMetaTypeDataUserData on FlatArrMetaTypeDataUserData.FlatArrayMetaTypeID=Fl atArrayMetaType.FlatArrayMetaTypeID
inner join Flat on FlatArrMetaTypeDataUserData.flatid =flat.flatid
--
-- @12 doesn't work
--
where FlatArrMetaTypeDataUserData in @12
END

 

[Pattycake245]

You cannot reference a list this way, as you have discovered. You would pass the list as normal but then you need to break it up, by putting it into a temp table or table variable and then referencing the temp table or table variable in your "in" statement. There are many posts about passing and processing lists in this forum so you can do a search on that.

Tim

 

[DigitalBuilder]

Which would be faster, nested selects or a temp tables?

 

[Pattycake245]

I have many stored procs where a list is passed in and I break them up and store it in a table variable and reference that. It is milliseconds to run. Something like:

CREATE PROCEDURE dbo.findcodes
@CodeList char(50)
--list looks like this ->'(1,2,3)'
as

declare @pos int

declare @CodeTable table(val int)

--takes out all quotes and brackets
SET @CodeList = replace(replace(ltrim(rtrim(@CodeList)),'(',''),')','') + ','

--loop through list
WHILE CHARINDEX(',', @CodeList) > 0
BEGIN
--get next comma position
SET @pos = CHARINDEX(',', @CodeList)

--insert next value into table
INSERT @CodeTable VALUES (LTRIM(RTRIM(LEFT(@CodeList, @pos - 1))))

--delete inserted value from list
SET @CodeList = STUFF(@CodeList, 1, @pos, '')
END

select code, codename
from Table_Codes
where code in(select val from @CodeTable)

Tim

 

[JamesLean]

Check out the FAQs:

Passing a list of values to a Stored Procedure
faq183-3979

Passing a list of values to a Stored Procedure (Part II)
faq183-5207

--James

 

[DigitalBuilder]

How much faster is this than in (array e.g. 1,2,3)