Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

T-SQL Arrays

Posted on 2004-10-27
10
Medium Priority
?
1,272 Views
Last Modified: 2012-06-21
I need to use an array in T-SQL, cant find anything that suggests that arrays exist.
0
Comment
Question by:itsallmeman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 11

Expert Comment

by:rdrunner
ID: 12420558
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
 

Author Comment

by:itsallmeman
ID: 12420612
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
 
LVL 6

Expert Comment

by:billy21
ID: 12420664
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 6

Expert Comment

by:billy21
ID: 12420701
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
 
LVL 6

Expert Comment

by:billy21
ID: 12420726
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
 
LVL 6

Accepted Solution

by:
OlegP earned 100 total points
ID: 12420728
Something you can solve by dynamic-sql
0
 
LVL 6

Expert Comment

by:billy21
ID: 12420841
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
 
LVL 11

Expert Comment

by:rdrunner
ID: 12421361
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
 
LVL 6

Expert Comment

by:billy21
ID: 12421422
>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
 
LVL 6

Expert Comment

by:billy21
ID: 12421450
>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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

618 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