Help with SQL query

Question

Hello,

I have list of DateTime values, and for each value I need to fetch something from the database. I would like to do this with one query. I know it's possible to pass a table (list) to the stored procedure, but Im not sure how to write the query itself.

Let's say I have the following table:

CREATE TABLE Shows(
    ShowId [int] NOT NULL, 
    StartTime DateTime NOT NULL, 
    EndTime DateTime NOT NULL 
)

and an array of dates

DECLARE @myDateArray MyCustomDateArrayType

Now, if I were fetching a single item, I would write a query like this:

SELECT * FROM Shows
WHERE StartTime > @ArrayItem and @ArrayItem < EndTime

where @ArrayItem is an item from @myDateArray .

But how do I formulate the query that would fetch the information for all array items?

Thanks!

Answer 1

This should do it:

SELECT s.* 
FROM Shows s
    JOIN @MyDateArray t ON s.StartTime > t.TableVarDateField 
        AND t.TableVarDateField < s.EndTime