Solved

array in sql 2008

Posted on 2012-03-20
10
237 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

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!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

730 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