Solved

array in sql 2008

Posted on 2012-03-20
10
236 Views
Last Modified: 2012-03-20
Hi

I know that there isn't an array type in SQL. Can anybody tell me how to reproduce the following vb code so I can use it in a stored procedure:

Private myArray() As Integer = New Integer() {5,7,3,7,2,1}

Dim temp As Integer = myArray(3)

John
0
Comment
Question by:IssacJones
  • 5
  • 3
  • 2
10 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 37741388
DECLARE @TEMP TABLE

(
  ID int,
  MyVal int

)

INSERT @TEMP VALUES(0,5);
INSERT @TEMP VALUES(1,7);
INSERT @TEMP VALUES(2,3);
INSERT @TEMP VALUES(3,7);
INSERT @TEMP VALUES(4,2);
INSERT @TEMP VALUES(5,1);


select * from @TEMP
0
 
LVL 18

Accepted Solution

by:
deighton earned 280 total points
ID: 37741408
or even

DECLARE @MyArray TABLE

(
  ID int,
  MyVal int

)

INSERT @MyArray VALUES(0,5);
INSERT @MyArray VALUES(1,7);
INSERT @MyArray VALUES(2,3);
INSERT @MyArray VALUES(3,7);
INSERT @MyArray VALUES(4,2);
INSERT @MyArray VALUES(5,1);

DECLARE @TEMP int;

select @TEMP = MyVal from @MyArray WHERE id = 3

SELECT @TEMP;
0
 

Author Comment

by:IssacJones
ID: 37741472
Cheers, as a matter of interest, I was trying to reproduce the following from some code from: http://www.sommarskog.se/arrays-in-sql-2008.html#TVP_in_TSQL



      CREATE TYPE INTEGER_LIST AS TABLE (n int NOT NULL PRIMARY KEY)
      
      DECLARE @MONTHDAY INTEGER_LIST

but I get the following error:

Msg 2715, Level 16, State 3, Procedure ComputeAgeYearsMonthsDays, Line 6
Column, parameter, or variable #7: Cannot find data type INTEGER_LIST.
Parameter or variable '@MONTHDAY' has an invalid data type.

This seems to follow the same idea i.e. create a table to mimic an array.

Can you tell me why I get the errror and how to resolve it?

John
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 18

Expert Comment

by:deighton
ID: 37741509
in the docs it seems to be FROM rather than AS, but I can't get any of it to work with any data type.
0
 

Author Comment

by:IssacJones
ID: 37741514
It fails at

 DECLARE @MONTHDAY INTEGER_LIST

strange!
0
 

Author Comment

by:IssacJones
ID: 37741570
Ah, I needed to do this

 DECLARE @MONTHDAY AS INTEGER_LIST

The document I referred to appears to be wrong(?)
0
 

Author Comment

by:IssacJones
ID: 37741737
I think I'm almost there - deighton can you see what is wrong with this code:

      CREATE TYPE INTEGER_LIST AS TABLE (n INT NOT NULL PRIMARY KEY, increment INT NOT NULL)
      
      DECLARE @MONTHDAY AS INTEGER_LIST
      
      INSERT INTO @MONTHDAY (n,increment) VALUES (0,31),(1,-1),(2,31),(3,30),(4,31),(5,30),
                                                 (6,31),(7,31),(8,30),(9,31),(10,30),(11,31)
      
      DECLARE @TEMP int;
      
      SELECT @TEMP = increment from @MONTHDAY WHERE n = 3

I get the following error:

Msg 207, Level 16, State 1, Procedure doStuff, Line 43
Invalid column name 'increment'.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37742436
You need to have a GO after CREATE TYPE.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 20 total points
ID: 37742478
Try it this way:
CREATE TYPE INTEGER_LIST AS TABLE (n INT NOT NULL PRIMARY KEY, increment INT NOT NULL)
GO
DECLARE @MONTHDAY AS INTEGER_LIST

Open in new window

0
 

Author Comment

by:IssacJones
ID: 37742479
Hi, yes,and I needed to drop the user defined table first i.e.

IF EXISTS(
      SELECT * FROM sys.types
      WHERE name = 'INTEGER_LIST'
)DROP TYPE dbo.INTEGER_LIST
GO

CREATE TYPE dbo.INTEGER_LIST AS TABLE
(
      n INT NOT NULL PRIMARY KEY,
      increment INT NOT NULL
)
GO
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

860 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