当前位置： 文江博客话题详情

Microsoft SQL Server 2005 函数，传递开始和结束时间列表

发布于 2024-08-29 09:04:26 字数 651 浏览 7 评论 0原文

我想要将动态数量的一个开始/结束时间对作为输入参数传递给函数。然后，该函数将使用该列表，而不仅仅是选择语句中的一个开始时间和一个结束时间。

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
原文

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（3）

萌能量女王 2024-09-05 09:04:26

您需要一种方法来拆分和处理 TSQL 中的字符串，有很多方法可以做到这一点。本文涵盖了几乎每种方法的优点和缺点：

"数组和列表SQL Server 2005 及更高版本，当表值参数无法分割时”作者：Erland Sommarskog

您需要创建一个拆分函数。这是分割函数的使用方式：

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

我更喜欢数字表方法在 TSQL 中分割字符串，但在 SQL Server 中分割字符串的方法有很多种，请参阅前面的链接，其中解释了每种方法的优点和缺点。

要使 Numbers Table 方法发挥作用，您需要执行此一次时间表设置，这将创建一个包含 1 到 10,000 行的 Numbers 表：

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)

设置 Numbers 表后，创建此拆分函数：

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

测试拆分：

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')

输出：

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)

请注意输入字符串中缺少值：

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

在函数的结果集中创建了一个空字符串值，CONVERT 将其更改为 1900-01-01 00:00:00.000，您可以使用CASE 语句以不同的方式处理这些。

然后创建你的函数。这是基于问题中的代码。我不确定它的作用，因为它返回一个 int，它始终为零，并且不对查询执行任何操作。但它来自 OPs 函数，所以它一定是他们正在做的事情的简单形式：

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

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
回复 原文
原野 2024-09-05 09:04:26

阅读 Erland Sommarskog 关于 SQL Server 2005 中的数组和列表的优秀（而且很长！）文章及其他 - 当 TVP 无法削减时。我打赌这不仅仅能解决您的问题:-)

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) :-)

回复 原文
寄居人 2024-09-05 09:04:26

我可能会使用 OpenXML 来实现。如果您想了解其工作原理，我写了关于它的一篇博客文章 。

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.

回复 原文
~没有更多了~

关于作者

一世旳自豪

暂无简介

0 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

留蓝

文章 0 评论 0

18790681156

文章 0 评论 0

zach7772

文章 0 评论 0

Wini

文章 0 评论 0

ayeshaaroy

文章 0 评论 0

初雪

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文