asked on

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

deighton

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

ASKER CERTIFIED SOLUTION

deighton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IssacJones

ASKER

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

deighton

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.

IssacJones

ASKER

It fails at

DECLARE @MONTHDAY INTEGER_LIST

strange!

IssacJones

ASKER

Ah, I needed to do this

DECLARE @MONTHDAY AS INTEGER_LIST

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

IssacJones

ASKER

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

Anthony Perkins

You need to have a GO after CREATE TYPE.

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IssacJones

ASKER

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