Multi-value date parameter in stored procedure?

问题:

I'm trying to get a stored procedure to work that accepts a multi-value parameter for dates. This isn't in SSRS but I'm trying to use the same approach as I do with it:

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

I use this approach with INT and VARCHAR fields on SSRS report multi-value parameters.

Here is the code I'm using to concatenate the 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;

Using this dbo.Split function I grabbed online:

/****** 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

I guess I'm not entirely clear on what differs between how I'm formatting the parameter, how SSRS does so for similar parameters (this is the only one I've tried doing from code), and how the Date data type affects required formatting. I'm getting a "Conversion failed when converting date and/or time from character string." error when selecting more than one value.

Edit: As requested, example of foreach loop output:

'9/9/2011', '8/19/2011', '8/12/2011'

回答1:

Why not use a Table-Valued parameter?

Create a user-defined table-type DateTimes on SQL

create type DateTimes as table
(
    [Value] datetime
)

Then amend your stored procedure:

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

Now you can treat @SourceFileDates as a readonly table-variable.

When specifying your SqlCommand parameters, a Table-Valued parameter is specified as SqlDbType.Structured and passed as a DataTable or DataRowcollection. So, you can populate it like so:

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
});

Now everything is nice and properly typed... and you don't have to do any string parsing or casting.

As a side-note, you might as well go ahead and create Strings and Integers types as well; You'll get hooked on TVPs and use them all over the place.

回答2:

SSRS cheats a bit because it's controlling the inputs... it's not so concerned about SQL Injection attacks. Through a stored procedure, this would be a bit more difficult to do.

What has worked well for me when I needed to send multiple values in a single argument in 2005, I would send them as an XML string like so:

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

and then treat this as a table in the function:

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

now you should have your data as table-valued. (syntax may be a little off, this is off the top of my head)