CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Nikki
Nikki

Reputation: 137

How to use Arrays in sql

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.

Upvotes: 0

Views: 155

Answers (4)

cyan
cyan

Reputation: 747

Declare a table variable

Declare @temp Table
(
 startdate datetime,
 enddate datetime
)

Table variables are alive for duration of the script running only

Upvotes: 0

user275683
user275683

Reputation:

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

Upvotes: 1

slapthelownote
slapthelownote

Reputation: 4279

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)

Upvotes: 1

El Ronnoco
El Ronnoco

Reputation: 11922

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

Upvotes: 0

Related Questions