array in sql 2008

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
IssacJonesAsked:
Who is Participating?
 
deightonConnect With a Mentor Commented:
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
 
deightonCommented:
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
 
IssacJonesAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
deightonCommented:
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
 
IssacJonesAuthor Commented:
It fails at

 DECLARE @MONTHDAY INTEGER_LIST

strange!
0
 
IssacJonesAuthor Commented:
Ah, I needed to do this

 DECLARE @MONTHDAY AS INTEGER_LIST

The document I referred to appears to be wrong(?)
0
 
IssacJonesAuthor Commented:
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
 
Anthony PerkinsCommented:
You need to have a GO after CREATE TYPE.
0
 
Anthony PerkinsConnect With a Mentor Commented:
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
 
IssacJonesAuthor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.