Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

array in sql 2008

Posted on 2012-03-20
10
Medium Priority
?
251 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 1120 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
Independent Software Vendors: 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!

 
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 80 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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 …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

572 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