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.

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. — talegna
– talegna, Commented Mar 12, 2014 at 12:06
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... — El Ronnoco
– El Ronnoco, Commented Mar 12, 2014 at 12:07
from the select statement i get multiple returns for startdate and end date. so i want to store them.. — Nikki
– Nikki, Commented Mar 12, 2014 at 12:08
i dont mind if i can select the values from "REquestModelMap1" other than what it is returning — Nikki
– Nikki, Commented Mar 12, 2014 at 12:09

user275683 · Accepted Answer · 2014-03-12 12:15:37Z

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

answered Mar 12, 2014 at 12:15

user275683

Sign up to request clarification or add additional context in comments.

3 Comments

Nikki Over a year ago

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 Over a year ago

I'm not exactly sure how you will related your dates to RequestModelMap1 table. I'm not following your logic.

Nikki Over a year ago

sorry its from table Reservations1

El Ronnoco · Accepted Answer · 2014-03-12 12:11:57Z

1

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)

edited Mar 12, 2014 at 12:11

El Ronnoco

11.9k5 gold badges41 silver badges67 bronze badges

answered Mar 12, 2014 at 12:10

slapthelownote

4,2791 gold badge26 silver badges27 bronze badges

1 Comment

Nikki Over a year ago

im using sql server 2008

El Ronnoco · Accepted Answer · 2014-03-12 12:11:39Z

0

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

answered Mar 12, 2014 at 12:11

El Ronnoco

11.9k5 gold badges41 silver badges67 bronze badges

1 Comment

Nikki Over a year ago

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

cyan · Accepted Answer · 2014-03-12 12:20:29Z

0

Declare a table variable

Declare @temp Table
(
 startdate datetime,
 enddate datetime
)

Table variables are alive for duration of the script running only

answered Mar 12, 2014 at 12:20

cyan

7475 silver badges8 bronze badges

Collectives™ on Stack Overflow

How to use Arrays in sql

4 Answers 4

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related