Solved

array in sql 2008

Posted on 2012-03-20
10
238 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
[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
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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…

726 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