I'd like to do had a dynamic number of one start/end time pairs passed to a function as an input parameter. The function would then use the list instead of just one start, and one end time in a select statement.
CREATE FUNCTION [dbo].[GetData]
(
@StartTime datetime,
@EndTime datetime
)
RETURNS int
AS
BEGIN
SELECT @EndTime = CASE WHEN @EndTime > CURRENT_TIMESTAMP THEN CURRENT_TIMESTAMP ELSE @EndTime END
DECLARE @TempStates TABLE
(StartTime datetime NOT NULL
, EndTime datetime NOT NULL
, StateIdentity int NOT NULL
)
INSERT INTO @TempStates
SELECT StartTime
, EndTime
, StateIdentity
FROM State
WHERE StartTime <= @EndTime AND EndTime >= @StartTime
RETURN 0
END
