T-SQL Arrays

I need to use an array in T-SQL, cant find anything that suggests that arrays exist.
itsallmemanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rdrunnerCommented:
You could use a table type variable in T-SQL. This would be as close as it gets to an array.


What do you need to do exactly?
0
itsallmemanAuthor Commented:
I am writing a stored procedure that should take some of the data from one table, change some of the values and stick them in another table. The problem is the amount of columns will vary from time to time. I would like the SP to function each time without me having to modify it.
The array would therefore come in useful, if I could declare one as large as the number of columns and then stick data into it for each row and INSERT into the other table.

Hope this explains it slightly more clearly, I get the feeling I am coming at this from the wrong side.
0
billy21Commented:
itsalleman,

I have tow methods that may help you out.  One is a udf that accepts a comma delimited string and converts it to a table.  You are limited to a single dimension and 8000 characters.  The other method is to use XML.  You can create a stored procedure with a parameter of Text or nText.  It can receive copious amounts of xml which is converted to a table through openxml().

which would you prefer?

There are no arrays in TSQL.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

billy21Commented:
Here's an example of openxml in action...


CREATE  Procedure dbo.SOSVaryVerify_Update(@UpdateXML Text)
AS
      DECLARE @idoc int
      DECLARE @RC int

      EXEC sp_xml_preparedocument @idoc OUTPUT, @UpdateXML

                Select *
      Into #Results
      From OpenXML (@idoc,'/Header/SOS',1)
            With (      Unit1 Real,
                  Unit2 Real,
                  Unit3 Real,
                  Unit4 Real,
                  Unit5 Real,
                  Unit6 Real,
                  Unit7 Real,
                  V1 Bit,
                  V2 Bit,
                  V3 Bit,
                  V4 Bit,
                  V5 Bit,
                  V6 Bit,
                  V7 Bit,
                  StartDate DateTime '../@StartDate',
                  SupplierId Int '../@SupplierId',
                  Id Int,
                  SPNo Int '../@SPId',
                  VariationReason Int '../@VariationReason',
                  VariationNote VarChar(1000) '../@VariationNote')
      EXEC sp_xml_RemoveDocument @idoc
Go
0
billy21Commented:
Here is the udf that converts a string to a table.  I think I said earlier it had to be delimited by a comma.  Not true you can delimit it with any character.

CREATE function  dbo.strSplit (@string VarChar(8000), @Delimiter Char( 1) )
Returns @res Table (ID BigInt Primary Key, val VarChar(8000) )
As
Begin
     If substring (@string, len ( @string), 1)<>@Delimiter
          Set @string= @String+@Delimiter

      Declare @Start int,
            @word varchar(8000),
            @CharIndex int,
            @i int,
            @Count Int

      set @i=1
      set @Start=1
      set @CharIndex= charindex(@Delimiter, @string, @start)
      set @Count = 0
      
      while (@charindex <> 0)begin
        set @Word= substring(@string, @start, @charindex - @start)
        set @Start= @charindex +1
        set @CharIndex= charindex(@Delimiter, @string, @start)
        Set @Count = @Count + 1
      
        Insert Into @res  values (@i,@word)
        set @i=@i+1
      end
      return
end
Go

--Example
Select * From dbo.strSplit ('Row1,Row2,Row3,Row4', ',')
0
OlegPCommented:
Something you can solve by dynamic-sql
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
billy21Commented:
Actually after reading your last comment it sounds like you need either a table variable or a temp table.

Table variable...

Declare @tbl Table(Field1 int, Field2 VarChar(10), Field3 DateTime) --Etc

Temp Table example1...
Create Table #MyTempTable(Field1 int, Field2 VarChar(10), Field3 DateTime) --Etc

Temp Table example2
Select *
Into #MyTempTable
From MyTable
0
rdrunnerCommented:
You can also create a temp table by selecting into it. It will create a table with the fields you selected and you only need to make sure you insert the fields into the right fields when you insert again.

select * into ##test from authors

This way you wont have to define the table you want to use. It will be created on the fly for you.

I hope this helps
0
billy21Commented:
>You can also create a temp table by selecting into it. It will create a table with the fields you selected

Yep, as I pointed out in temp tables example 2 ;-)
0
billy21Commented:
>select * into ##test from authors

Note that prefixing a temp table with 2 hashes instead of 1 makes it available outside of the scope of your stored procedure.  If this isn't necessary you should always prefix with only 1 #.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.