Microsoft SQL Server 2005 Function, passing list of start and end times

Question

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

Answer 1

The way I would probably do it is using OpenXML. I wrote a blog article about it if you want to see an intro view on how it works.

Answer 2

Read Erland Sommarskog's excellent (and long!) article on Arrays and Lists in SQL Server 2005 and Beyond - When TVPs Do Not Cut it. I bet that'll more than cover your question(s) :-)

Answer 3

You need a way to split and process the string in TSQL, there are many ways to do this. This article covers the PROs and CONs of just about every method:

"Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog

You need to create a split function. This is how a split function can be used:

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

I prefer the number table approach to split a string in TSQL but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.

For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers that contains rows from 1 to 10,000:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, create this split function:

CREATE FUNCTION [dbo].[FN_ListToTableRows]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(
    ----------------
    --SINGLE QUERY-- --this will return empty rows, and row numbers
    ----------------
    SELECT
        ROW_NUMBER() OVER(ORDER BY number) AS RowNumber
            ,LTRIM(RTRIM(SUBSTRING(ListValue, number+1, CHARINDEX(@SplitOn, ListValue, number+1)-number - 1))) AS ListValue
        FROM (
                 SELECT @SplitOn + @List + @SplitOn AS ListValue
             ) AS InnerQuery
            INNER JOIN Numbers n ON n.Number < LEN(InnerQuery.ListValue)
        WHERE SUBSTRING(ListValue, number, 1) = @SplitOn
);
GO 

test out the splitting:

SELECT
    RowNumber, CONVERT(datetime,ListValue) AS ListValue 
    FROM  dbo.FN_ListToTableRows(',','1/1/2010 12:45am,,2/2/2010 1:23pm,3/3/2010 12:45')

OUTPUT:

RowNumber            ListValue
-------------------- -----------------------
1                    2010-01-01 00:45:00.000
2                    1900-01-01 00:00:00.000
3                    2010-02-02 13:23:00.000
4                    2010-03-03 12:45:00.000

(4 row(s) affected)

note that the missing value in the input string:

'1/1/2010 12:45am,,2/2/2010 1:23pm,3/3/2010 12:45'
                 ^^

created an empty string value in the function's result set, which the CONVERT changed to 1900-01-01 00:00:00.000, you could use a CASE statement to handle these in a different manner.

then create your function. This is based on the code int the question. I'm not sure what it does, because it returns an int, which is always zero, and doesn't do anything with the query. But it from the OPs function, so it must be a simple form of something they are doing:

CREATE FUNCTION [dbo].[GetData]
(
    @StartTime varchar(8000),  --CSV string of dates: '1/1/2010 12:45am,,2/2/2010 1:23pm,3/3/2010 12:45'
    @EndTime varchar(8000)     --CSV string of dates: '1/1/2010 12:45am,,2/2/2010 1:23pm,3/3/2010 12:45'
)
RETURNS int
AS
BEGIN
    DECLARE @TempStates TABLE
        (StartTime datetime NOT NULL
        , EndTime datetime NOT NULL
        , StateIdentity int NOT NULL
        )

    INSERT INTO @TempStates
    SELECT s.StartTime
        , s.EndTime
        , s.StateIdentity
    FROM State s
        CROSS JOIN (SELECT
                        a1.RowNumber
                            ,CONVERT(datetime,a1.ListValue) AS StartTime
                            ,CASE
                                WHEN a2.ListValue > GETDATE() THEN GETDATE()
                                ELSE CONVERT(datetime,a2.ListValue)
                            END AS EndTime
                        FROM dbo.FN_ListToTableRows(',',@StartTime)          a1
                            INNER JOIN dbo.FN_ListToTableRows(',',@EndTime)  a2 ON a1.RowNumber=a2.RowNumber
                   ) dt
    WHERE s.StartTime <= dt.EndTime AND s.EndTime >= dt.StartTime
    RETURN 0
END
GO