• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1301
  • Last Modified:

T-SQL Arrays

I need to use an array in T-SQL, cant find anything that suggests that arrays exist.
1 Solution
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?
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.

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.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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

CREATE  Procedure dbo.SOSVaryVerify_Update(@UpdateXML Text)
      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
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) )
     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

Select * From dbo.strSplit ('Row1,Row2,Row3,Row4', ',')
Something you can solve by dynamic-sql
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
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
>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 ;-)
>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 #.
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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now