CODEWITHSUNDEEP
sqlsql-server-2008parameters
user154301
user154301

Reputation: 85

SQL query passing table to stored procedure

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 I'm 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?

Upvotes: 2

Views: 82

Answers (1)

AdaTheDev
AdaTheDev

Reputation: 147224

This should do it:

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

Upvotes: 2

Related Questions