T-SQL Arrays

Posted on 2004-10-27
Last Modified: 2012-06-21
I need to use an array in T-SQL, cant find anything that suggests that arrays exist.
Question by:itsallmeman
    LVL 11

    Expert Comment

    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?

    Author Comment

    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.
    LVL 6

    Expert Comment


    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.
    LVL 6

    Expert Comment

    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
    LVL 6

    Expert Comment

    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', ',')
    LVL 6

    Accepted Solution

    Something you can solve by dynamic-sql
    LVL 6

    Expert Comment

    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
    LVL 11

    Expert Comment

    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
    LVL 6

    Expert Comment

    >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 ;-)
    LVL 6

    Expert Comment

    >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 #.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    SQL Server Side Trace is a technique of Profiling SQL Server Events Silently (i.e without Using the Profiling Tool). Running a visual tool in production increases overhead, but we can develop server side Trace using Sql Server Profiler itself. We…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now