How to simulate an array in SQL Server

1 Sep 2005 11:22 AM

mal_k100

Just thought some might be interested in how to identify individual
items in an 'array' by declaring a local variable as table
--------------------------------------------------------------------
declare @MyTable table (MyRowID int identity, MyData varchar(255))
declare @MyCurrentRow as int
declare @MyRowCount as int
insert into @MyTable select Name from sysobjects -- (or whatever)
set @MyRowCount = @@RowCount
-- @@Rowcount hold the count of the last table in these cases anyway
-- then you can use statement to access the nth. row in the 'array'
select MyData from @MyTable where MyRowID = 5 -- as an example

--
-- or step through the table using @MyCurrentRow
Set @MyCurrentRow = 1
while @MyCurrentRow < @MyRowCount +1
Begin
Select MyData from @MyTable where MyRowID = @MyCurrentRow
set @MyCurrentRow = @MyCurrentRow + 1
Continue
end

Just thought it might help. It isn't rocket science but I saw a few
with problems so thought I'd post.
I needed it to obtain individual values from a table and use those
values to call a stored procedure repeatedly.
Cheers

1 Sep 2005 12:26 PM

Andrew J. Kelly

See if these help:

http://www.aspfaq.com/show.asp?id=2248 Arrays & Lists
http://www.sommarskog.se/dynamic_sql.html Dynamic SQL
http://www.users.drew.edu/skass/sql/ListToTableProc.sql.txt List to
Table script

--
Andrew J. Kelly SQL MVP

<mal_k***@hotmail.com> wrote in message

Show quote

news:1125573743.271138.167070@o13g2000cwo.googlegroups.com...
> Just thought some might be interested in how to identify individual
> items in an 'array' by declaring a local variable as table
> --------------------------------------------------------------------
> declare @MyTable table (MyRowID int identity, MyData varchar(255))
> declare @MyCurrentRow as int
> declare @MyRowCount as int
> insert into @MyTable select Name from sysobjects -- (or whatever)
> set @MyRowCount = @@RowCount
> -- @@Rowcount hold the count of the last table in these cases anyway
> -- then you can use statement to access the nth. row in the 'array'
> select MyData from @MyTable where MyRowID = 5 -- as an example
> --
> -- or step through the table using @MyCurrentRow
> Set @MyCurrentRow = 1
> while @MyCurrentRow < @MyRowCount +1
> Begin
> Select MyData from @MyTable where MyRowID = @MyCurrentRow
> set @MyCurrentRow = @MyCurrentRow + 1
> Continue
> end
>
> Just thought it might help. It isn't rocket science but I saw a few
> with problems so thought I'd post.
> I needed it to obtain individual values from a table and use those
> values to call a stored procedure repeatedly.
> Cheers
>