How to use Arrays in sql

Question

I have a select statement where in it might return more than one value. so I would like to store the selected values in a table kind of array since there is no concept of array in sql and also use the same stored values. My select query is as follows:

 if exists(
 Select StartDate,EndDate 
 FROM Reservations1 
 where (DateAdd(day,0,StartDate) >= @StartDate)
 and (DateAdd(day,0,EndDate) <= @EndDate) 
 and Requestid in
 (Select RequestId from RequestModelMap1 
 where ModelSerialNumber=@ModelSerialNumber)
 )

If it returns some values then i want to store it.

• talegna: Which database system are you using (SQL Server, Oracle, etc)? Is this a stored procedure? How is it being interpreted? Please put more details on your question.
• El Ronnoco: More details please - what exactly are you wanting to do with the values? And which values are they? For example your IN clause is effectively specifying an array of values (RequestIDs) as an argument...
• Nikki: im using sql server 2008
• Nikki: from the select statement i get multiple returns for startdate and end date. so i want to store them..
• Nikki: i dont mind if i can select the values from "REquestModelMap1" other than what it is returning

Answer 1

Depending on the size of "array" you might just want to store it in table variable. Smaller sets I store in table variables, but larger I store in temp tables.

DECLARE @MyDateArray TABLE
 (
 StartDate DATETIME
 ,EndDate DATETIME
 )
INSERT INTO @MyDateArray
 ( StartDate
 ,EndDate
 )
 SELECT StartDate
 ,EndDate
 FROM Reservations1
 WHERE ( DATEADD(day, 0, StartDate) >= @StartDate )
 AND ( DATEADD(day, 0, EndDate) <= @EndDate )
 AND Requestid IN ( SELECT RequestId
 FROM RequestModelMap1
 WHERE ModelSerialNumber = @ModelSerialNumber )
SELECT *
FROM @MyDateArray

• Nikki: this is somewhat close to what i want. ok from whatever is stored in this temp table i would like to select values from this "RequestModelMap1" table other than what is there in temp table and return this as outpu
• user275683: I'm not exactly sure how you will related your dates to RequestModelMap1 table. I'm not following your logic.
• Nikki: sorry its from table Reservations1

Answer 2

Some pseudo SQL since I'm not sure what db system you are using:

-- create a table to store results
CREATE TABLE SelectedDates
(
 StartDate DATETIME,
 EndDate DATETIME
);
-- empty it
TRUNCATE TABLE SelectedDates
-- insert data
INSERT INTO SelectedDates (StartDate, EndDate)
 Select StartDate,EndDate 
 FROM Reservations1 
 where (DateAdd(day,0,StartDate) >= @StartDate)
 and (DateAdd(day,0,EndDate) <= @EndDate) 
 and Requestid in
 (Select RequestId from RequestModelMap1 
 where ModelSerialNumber=@ModelSerialNumber)

• Nikki: im using sql server 2008

Answer 3

If you want to store the values temporarily you can select into a temp table eg...

 Select StartDate,EndDate 
 INTO #temp
 FROM Reservations1 
 where (DateAdd(day,0,StartDate) >= @StartDate)
 and (DateAdd(day,0,EndDate) <= @EndDate) 
 and Requestid in
 (Select RequestId from RequestModelMap1 
 where ModelSerialNumber=@ModelSerialNumber)
 )
 --Show that values have been stored...
 SELECT * FROM #temp

However this may not been what you need - or the best way. You question requires more detail...

• Nikki: this is somewhat close to what i want. ok from whatever is stored in this temp table i would like to select values from this "RequestModelMap1" table other than what is there in temp table and return this as output

Answer 4

Declare a table variable

Declare @temp Table
(
 startdate datetime,
 enddate datetime
)

Table variables are alive for duration of the script running only