存储过程中的多值日期参数

本文关键字：日期 参数 存储过程 过程中 存储 | 更新日期: 2023-09-27 18:09:03

我试图让一个存储过程的工作，接受多值参数的日期。这不是在SSRS中，但我试图使用与它相同的方法:

ALTER PROCEDURE spSelectPlacementData
(
    @ClientID           SMALLINT,
    @SourceFileDates    VARCHAR(MAX)
)
AS
BEGIN
    SELECT (snip)
    FROM [APS].[dbo].[Account] A
    WHERE ClientID = @ClientID
    AND A.[SourceFileDate] IN (SELECT * FROM dbo.Split(@SourceFileDates))
END

我在SSRS报告多值参数的INT和VARCHAR字段中使用这种方法。

下面是我用来连接SourceFileDates的代码:

    string sourceFileDates = "";
    foreach (DateTime file in job.sourceFiles)
    {
        if (file == job.sourceFiles.Last())
        {
            sourceFileDates += "'" + file.ToString("d") + "'";
        }
        else
        {
            sourceFileDates += "'" + file.ToString("d") + "', ";
        }
    }
    selectRunCommand = new SqlCommand("spSelectPlacementData", sqlConnection);
    selectRunCommand.CommandType = CommandType.StoredProcedure;
    selectRunCommand.Parameters.Add("@ClientID", SqlDbType.SmallInt);
    selectRunCommand.Parameters["@ClientID"].Value = job.clientID;
    selectRunCommand.Parameters.Add("@SourceFileDates", SqlDbType.VarChar);
    selectRunCommand.Parameters["@SourceFileDates"].Value = sourceFileDates;

使用这个dbo。我在网上抓取的拆分函数:

/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 09/20/2011 11:16:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split]
/* This function is used to split up multi-value parameters */
(
@ItemList VARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(MAX) collate database_default )
AS
BEGIN
DECLARE @tempItemList VARCHAR(MAX)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item VARCHAR(MAX)
SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ', @delimiter)
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END

我想我不完全清楚我如何格式化参数，SSRS如何对类似的参数进行格式化(这是我唯一尝试过的代码)，以及日期数据类型如何影响所需的格式之间的区别。当选择多个值时，出现"从字符串转换日期和/或时间时转换失败"错误。

编辑:按要求，foreach循环输出示例:

'9/9/2011'， '8/19/2011'， '8/12/2011'

存储过程中的多值日期参数

为什么不使用表值参数?

在SQL

上创建一个用户定义的表类型DateTimes
create type DateTimes as table
(
    [Value] datetime
)

然后修改存储过程:

ALTER PROCEDURE spSelectPlacementData
(
    @ClientID           SMALLINT,
    @SourceFileDates    DateTimes readonly -- must be readonly
)

现在您可以将@SourceFileDates视为一个只读表变量。

指定SqlCommand参数时，表值参数指定为SqlDbType.Structured，并作为DataTable或DataRowcollection传递。所以，你可以像这样填充它:

var sourceFileDates = new DataTable();
sourceFileDates.Columns.Add("Value", typeof(DateTime));
foreach (DateTime file in job.sourceFiles)
{
    sourceFileDates.Rows.Add(file);
}
selectRunCommand.Parameters.Add(new SqlParameter {
    ParameterName = "@SourceFileDates", 
    Value = sourceFileDates,
    SqlDbType = SqlDbType.Structured // make sure you specify structured
});

现在一切都很好，输入正确…并且您不需要做任何字符串解析或强制转换。

作为旁注，您也可以继续创建Strings和Integers类型;你会迷上电视，到处使用它们。

SSRS有点作弊，因为它控制输入…它不太关心SQL注入攻击。如果使用存储过程，这就有点困难了。

在2005年，当我需要在单个参数中发送多个值时，我将它们作为XML字符串发送，如:

<dates>
    <date>2011-01-23</date>
    <date>2011-02-24</date>
</dates>

，然后将其作为函数中的表处理:

select 
    x.a.value('.', 'datetime') as myDate
from
    @XMLArg.nodes('/dates/date') x(a);

现在您应该将数据作为表值。(语法可能有点偏离，这是我的头)

相关文章：