Have Geek Brain

Stored procedure SQL pass table

I have a list of DateTime values ​​and for each value I need to retrieve something from the database. I would like to do this with a single request. I know it is possible to pass a table (list) to a 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 to retrieve one item, I would write a query like this:

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

where @ArrayItem is the item from @myDateArray.

But how do I formulate a query that will retrieve information for all elements of an array?

+2


a source to share


1 answer


This should do it:



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


a source






More articles:


All Articles